PostgreSQL – DISTINCT ON clause

In Postgres database, you can use DISTINCT ON to select one row per group.

Consider the below employee table; where we have employee_id, department and salary fields.

employee_id	department	salary
1	Accounts	40,000
2	Accounts	50,000
3	Sales	50,000
4	Sales	60,000
5	Sales	70,000

Below is the query to identify the employees with highest salary in each department.

SELECT DISTINCT ON (department)
department,employee_id,salary
order by department,salary desc,employee_id;

‘DISTINCT ON department’ will return only 1 row per department.

I have used ‘salary desc’ in the order by clause to return the employee with highest salary. Without the ‘salary desc’ in the order by clause the result will be unpredictable. You will receive “any” 1 employee from each department without the ‘salary desc’ clause.

I have added employee_id to the order by clause to return the employee with least employee_id in case there are more than 1 employees with same salary within a department.

You must always use the DISTINCT ON expression as the first expression in the ORDER BY clause. Thus the department appears first in the order by list.