Re: How do I write SQL
Interesting...
>From: rich <richn@northv.com>
>Date: Mon, 10 Feb 1997 13:54:15 -0500
>X-Informix-List-Id: <news.33719>
>
>How do I write SQL to perform the following:
>
> I have a table that contains prices for five years. I need to select the last
>price posted in each month. The record format is key,date,value -- the data in
>any one month could be one record or up to twenty records. I need the output to
>show the date and value.
You can get the key and date (plus two necessary but unwanted other values)
using:
SELECT Key,
YEAR(DateCol) AS Year,
MONTH(DateCol) AS Month,
MAX(DateCol) AS DateCol
FROM WhicheverTableItIs
GROUP BY 1, 2, 3
INTO TEMP AggregateData;
You can then answer your real question using:
SELECT W.Key, W.DateCol, W.Value
FROM WhicheverTableItIs W, AggregateData A
WHERE A.Key = W.Key
AND A.DateCol = W.DateCol
ORDER BY Key, DateCol;
This is a classic case of trying to mix aggregates and non-aggregates in a
single SQL query, and it cannot be done in pre-SQL92 databases without
using extensions such as temporary tables. In full SQL92, you could write
this as a single query along the lines of this (untested syntax):
SELECT W.Key, W.DateCol, W.Value
FROM WhicheverTableItIs W,
(SELECT Key,
YEAR(DateCol) AS Year,
MONTH(DateCol) AS Month,
MAX(DateCol) AS DateCol
FROM WhicheverTableItIs
GROUP BY 1, 2, 3
) AS AggregateData
WHERE AggregateData.Key = W.Key
AND AggregateData.DateCol = W.DateCol
ORDER BY Key, DateCol;
The YEAR() and MONTH() functions may or may not be in SQL92 -- it's all a
bit academic as Informix does not, as far as I know, support the
nested-query syntax in this form.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>