Re: SQL question: GROUP BY and MAX

"Matti Teppo" <matti.teppo_at_nospam.deio.net> wrote in message news:3B053BEA.30C3F729_at_nospam.deio.net...
> I have the following table:
> select * from revisions
> order by firstid, rev;
> id firstid rev name
> ----------- ----------- ----------- ----
> 100 100 0 Donald
> 101 100 1 Donald D.
> 103 100 2 Donald Duck
> 102 102 0 Mickey
> 104 102 1 Mickey Mouse
> 105 105 0 Charlie Brown
>
> I want for each firstid the row that has the highest rev value. The
> desired result set is:
> id firstid rev name
> ----------- ----------- ----------- ----
> 103 100 2 Donald Duck
> 104 102 1 Mickey Mouse
> 105 105 0 Charlie Brown

The following should work:

SELECT * FROM revisions
WHERE id IN (SELECT MAX(id)
FROM revisions
GROUP BY rev)

<Pierre/> Received on Sat May 19 2001 - 05:38:23 CEST