Re: SQL question: GROUP BY and MAX

> The following should work:
>
> SELECT * FROM revisions
> WHERE id IN (SELECT MAX(id)
> FROM revisions
> GROUP BY rev)

Yes, it works fine with the data I posted. I now see that my example was a little bit incomplete. The id values are not always in ascending order, they are just unique keys. This is the revised table contents: select * from revisions
order by firstid, rev
id firstid rev col1 ----------- ----------- ----------- ----

        100         100           0 Donald
        101         100           1 Donald D.
         99         100           2 Donald Duck
        102         102           0 Mickey
        104         102           1 Mickey Mouse
        105         105           0 Charlie Brown
        107         107           0 Obelix
        106         107           1 Obelix the Gaul

And this is the desired result set:
id          firstid     rev         col1
----------- ----------- ----------- ----
         99         100           2 Donald Duck
        104         102           1 Mickey Mouse
        105         105           0 Charlie Brown
        106         107           1 Obelix the Gaul

The question in English is: "For each firstid value, get the row that has the highest rev value."

This works in Sybase but not in Oracle:
select *
from revisions
group by firstid
having rev = max(rev)

I'm looking for a common solution for both DBMSs.Creating a view (select firstid, max(rev) as maxrev from revisions group by firstid) and joining to it is maybe the only solution. Any way to make it without the view?

(This is to create the sample:
create table revisions
(id int not null, firstid int not null, rev int not null, col1 varchar(15) not null)
insert into revisions
values (100, 100, 0, 'Donald')
insert into revisions
values (101, 100, 1, 'Donald D.')
insert into revisions
values (102, 102, 0, 'Mickey')
insert into revisions
values (99, 100, 2, 'Donald Duck')
insert into revisions
values (104, 102, 1, 'Mickey Mouse')
insert into revisions
values (105, 105, 0, 'Charlie Brown')
insert into revisions
values (107, 107, 0, 'Obelix')
insert into revisions
values (106, 107, 1, 'Obelix the Gaul')
)

Matti Received on Sun Jul 22 2001 - 01:25:38 CEST