SQL question: GROUP BY and MAX

From: Matti Teppo <matti.teppo_at_nospam.deio.net>
Date: Fri, 18 May 2001 17:12:42 +0200
Message-ID: <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

In Sybase, but not in Oracle, I can write: select *
from revisions
group by firstid
having rev = max(rev)

For Oracle, I kindly got the following solution in the Oracle newsgroup, but unfortunately it does not work in Sybase: 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

Any idea how to write this with such SQL that is good for both DBMSs?

Matti Received on Fri May 18 2001 - 17:12:42 CEST