PostgreSQL MAX and GROUP BY

Question

The shortest (and possibly fastest) query would be with DISTINCT ON, a PostgreSQL extension of the SQL standard DISTINCT clause:

SELECT DISTINCT ON (1)
       id, count, year
FROM   tbl
ORDER  BY 1, 2 DESC, 3;

The numbers refer to ordinal positions in the SELECT list. You can spell out column names for clarity:

SELECT DISTINCT ON (id)
       id, count, year
FROM   tbl
ORDER  BY id, count DESC, year;

The result is ordered by id etc. which may or may not be welcome. It’s better than “undefined” in any case.

It also breaks ties (when multiple years share the same maximum count) in a well defined way: pick the earliest year. If you don’t care, drop year from the ORDER BY. Or pick the latest year with year DESC.

For many rows per id, other query techniques are (much) faster. See:

Select first row in each GROUP BY group?
Optimize GROUP BY query to retrieve latest row per user

Leave a Comment Cancel reply