Re: SQL question: GROUP BY and MAX

Matti Teppo wrote:
> 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?

SELECT *
FROM revisions r1
WHERE r1.rev IN ( SELECT MAX(r2.rev)

                  FROM revisions r2
                  WHERE r1.firstid = r2.firstid );

But I didn't test this.

-- 
  Jan Hidders