Re: SQL question: SELECT and GROUP BY in Oracle8

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 17 May 2001 15:15:13 +0200
Message-ID: <9e0it6$792$1@s1.read.news.oleane.net>

"Matti Teppo" <matti.teppo_at_nospam.datex-ohmeda.com> a écrit dans le message news: 3B03C465.6E5997E3_at_nospam.datex-ohmeda.com...
> 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
>
> In Sybase I can write:
> select *
> from revisions
> group by firstid
> having rev = max(rev)
>
> But this does not work in Oracle, probably because having more columns
> in the select list than is in the GROUP BY clause is a Sybase extension.
> If I put all columns in SELECT and GROUP BY statements, I naturally get
> all rows.
>
> Any idea how to write this SQL for Oracle?
>
> Matti
>

select b.*
from revisions b,

     (select firstid, max(rev) maxrev
      from revisions group by firstid) a

where b.firstid = a.firstid
  and b.rev = a.maxrev
/

--
Hope this helps
Michel

Received on Thu May 17 2001 - 08:15:13 CDT