PostgreSQL DISTINCT ON

Getting the highest (or lowest) value from a database column is a bit tricky if you cannot use GROUP BY, because it requires you to aggregate across all columns that you want in the result. Say you have a table with employees, having the columns name, salary, and department, and you want to know the highest-paid employee per department. Then GROUP BY is not an option because you would need to also aggregate by name to have the name in the output, which doesn’t make sense.

I used to tackle this kind of query with unwieldy subqueries, but I just came across PostgreSQL’s DISTINCT ON clause, which makes them a lot more compact and readable. Using that, you can simply do

SELECT DISTINCT ON (department)
       name, salary, department
FROM   salaries_table
ORDER BY department, salary DESC;

So we’ll only get one entry per department, and ORDER BY salary DESC makes sure it is the one with the highest salary. The only bummer is that it is a PostgreSQL-specific function, so it won’t work on other DBMSs.

Leave a Reply

Your email address will not be published. Required fields are marked *