Re: How do I write SQL

Doug - I'm repeating myself for the benefit of the c.d.i readers: }Date: Mon, 10 Feb 1997 23:21:59 -0800 }From: Douglas Wilson <dgwilson@gte.net> } }Jonathan Leffler wrote: }> > 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. } }> 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; } }Which version did this become legal in? I like it. That syntax is not legal Informix syntax (up to 7.21, anyway). It is more or less legal in SQL92, give or take syntax errors. }Never being one to accept the impossible, how about the }following inefficient SQL: } }select t1.key, t1.datecol, t1.value } from thistable t1 } where t1.datecol= } (select max(datecol) } from thistable t2 } where datetime(t1.datecol year to month)=datetime(t2.datecol year to month) } and t1.key = t2.key) This correlated sub-query will also work (and using EXTEND/DATETIME in my query would reduce the number of columns which have to be processed and sorted). I doubt that the correlated subquery will be as fast as the temp table solution on reasonably large data sets. As in Perl, there's more than one way to do it (TMTOWTDI - timtoady). Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>