Re: SQL question: GROUP BY and MAX

Matti Teppo wrote:
> Jan Hidders wrote:
> >
> > SELECT *
> > FROM revisions r1
> > WHERE r1.rev IN ( SELECT MAX(r2.rev)
> > FROM revisions r2
> > WHERE r1.firstid = r2.firstid );
>
> Yes, this works!

Great. Nice to hear. :-)

> But how come the GROUP BY can be replaced with a join between the
> main query and subquery???

Because you can often replace the aggregate function in a HAVING condition by a subquery over the same table that is joined with the main query on the grouping attributes. See for example:

SELECT a
FROM tabA
GROUP BY a
HAVING COUNT(*) = 5; This is equal to:

SELECT DISTINCT a
FROM tabA r1
WHERE ( SELECT COUNT(*)

        FROM tabA r2
        WHERE r1.a = r2.a ) = 5;

-- 
  Jan Hidders