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
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.
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.