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.
