SQL question: SELECT and GROUP BY in Oracle8

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 Received on Thu May 17 2001 - 07:30:30 CDT