Re: How do I write SQL

Jonathan Leffler wrote: > > } > }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. > }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 extend(t1.datecol year to month)=extend(t2.datecol year to month) > } and t1.key = t2.key) (syntax errors corrected I hope) > > 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. I agree that the temp table solution is more efficient(notice my comment right before the SQL). I just enjoy it when someone asks "Can this be done in a single SQL statement?", so I'll usually give it a try if it's interesting problem. They never ask "What's the most efficient way to do this?", but I'll usually try to throw in that answer too if it hasn't already been given. If I understand correctly, the Universal Server allows functions in the index, so if you could index on "key, extend(...)" above, it might not be too bad. Douglas Wilson