Re: SQL question: SELECT and GROUP BY in Oracle8

I guess you have to use a view definition to keep it in both databases:

create view v_max_revs
as
select firstid, max (rev) as maxrev
from revisions
group by firstid
/

select b.*
from revisions b, v_max_revs a
where b.firstid = a.firstid
and b.rev = a.maxrev
/

Martin

Matti Teppo wrote:
>
> >
> > 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
> >
>
> Thank you, your solution works fine in Oracle. But it does not work in Sybase.
> Ideally I'd like to use the same query in my application for both DBMSs.
>
> I was thinking about putting the max part into a subquery but the problem is that the
> subquery would return a combination key (firstid and maxrev) for joining with main
> query.
>
> Matti
Received on Sun May 20 2001 - 23:37:05 CDT