List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 9 1999 8:38pm
Subject:Re: Request for SELECT statement help
View as plain text  
Jules Bean wrote:
> 
> Matt Loschert wrote:
> >
> > Hello all:
> >
> > I appologize in advance if this is a simple question or if I am asking
> > this question in the wrong place.  Feel free to point me to a more
> > appropriate place.
> 
> No worries.  But you'll save yourself time by getting yourself a decent
> SQL book (avoid anything that mentions Microsoft on the cover).
> 
> >
> > Otherwise, her is my problem.  I have a table which may be generalized as
> > containing a non-unique id field, a date field, and other fields.  I wish
> > to assemble a SELECT statement that selects the row from the table that
> > contains the desired id and the most recent date for that id, something
> > somewhat like this:
> >
> > SELECT   id, date, else
> > FROM     table
> > WHERE    id = 'such-and-such'
> > ORDER BY date DESC
> > LIMIT    1;
> >
> > The catch is that this is being inserted into a larger table join that
> > will therefore include more than one id.  I thus will need a way to pull
> > the most recent row (by date) for each id supplied, therefore making the
> > LIMIT clause useless (I think).
> >
> 
> You want an 'aggregate' function, which applies to GROUPed rows.  In
> this case, you want the MAX()imum date.  E.g.:
> 
> SELECT id,MAX(date) FROM table WHERE id='such-and-such' GROUP BY id;
> 
> This GROUPs the results by id, and within each group, it takes the MAX
> of the date field.
> 
> There is, however, no straight-forward way to get the 'else' columns.
> Either you need a second query, or you need to join the table to itself,
> something like (untested)
> 
> SELECT t1.id,MAX(t1.date),t2.else FROM table AS t1, table AS t2 WHERE
> t1.id=t2.id AND t1.id='such-and-such' AND t1.date = t2.date GROUP BY
> t1.id;
> 
> Jules

Hi Jules

The second one doesn't work!
This is, because the Grouping is done on the resultset of the WHERE part.
So there will be the first t2.else which is found and not the one belonging to the MAX(..)
part.

So the only way to do this is:
1) generate temp table:
CREATE tempTable (id int, date DATE);
2) fill it from the original table:
INSERT INTO tempTable SELECT id, MAX(date) FROM origTable WHERE id='such-and-such' GROUP
BY id;
3) get the desired data:
SELECT o.id, o.date, o.else FROM origTable AS o, tempTable AS t 
WHERE o.id = t.id AND o.date = t.date;
4) garbage collect:
DROP tempTable;

Tschau
Christian

Thread
Request for SELECT statement helpMatt Loschert9 Aug
  • Re: Request for SELECT statement helpJules Bean9 Aug
  • Re: Request for SELECT statement helpChristian Mack9 Aug
  • Re: Request for SELECT statement helpJules Bean9 Aug
    • Re: Request for SELECT statement helpMichael Widenius17 Aug
Re: Request for SELECT statement helpGreg Coladonato10 Aug
  • Re: Request for SELECT statement helpChristian Mack10 Aug