Re: Problem with aggregate functions and GROUP BY

Alex,

> which is the highest value of 'sortby' for each value of 'fk'.
>
> I would think that the SQL to achieve this is:
>
> SELECT * FROM test GROUP BY fk HAVING sortby = MAX(sortby);

You need a subselect for this:

SELECT test.*
FROM test, (SELECT max(sortby) as maxsort, fk
FROM test
GROUP BY fk) maxtest
WHERE test.fk = maxtest.fk
AND test.sortby = maxtest.maxsort;

There's also a pgsql extension called SELECT DISTINCT ON, but it's not
SQL 92 standard so I don't reccomend it.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco