Re: How do I write SQL
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.
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)
Douglas Wilson
** The opinions expressed here are not those of ... a complete idiot **