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
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(..)
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
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: