Reply-To: "Pierre G. Boutquin" <boutquin@home.com>
From: "Pierre G. Boutquin" <boutquin@home.com>
Newsgroups: comp.databases.theory
References: <3B053BEA.30C3F729@nospam.deio.net>
Subject: Re: SQL question: GROUP BY and MAX
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <PUlN6.130935$2_.41282571@news3.rdc1.on.home.com>
Date: Sat, 19 May 2001 03:38:23 GMT
NNTP-Posting-Host: 24.42.116.59
X-Complaints-To: abuse@home.net
X-Trace: news3.rdc1.on.home.com 990243503 24.42.116.59 (Fri, 18 May 2001 20:38:23 PDT)
NNTP-Posting-Date: Fri, 18 May 2001 20:38:23 PDT
Organization: Excite@Home - The Leader in Broadband http://home.com/faster


"Matti Teppo" <matti.teppo@nospam.deio.net> wrote in message
news:3B053BEA.30C3F729@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/>