Re: Problem with aggregate functions and GROUP BY - Mailing list pgsql-sql

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco