PostgreSQL SELECT DISTINCT

In SQL DISTINCT means what you would expect, returns distinct values. As an example, let's say you want a distinct list of departments from your employee's table. The following SQL will return that distinct list.

SELECT DISTINCT department FROM employee;

You can also select distinct data across multiple columns. Now let's say you want to see unique job titles per department you could simply select both columns names after the DISTINCT clause.

SELECT DISTINCT department, job_title FROM employee;

Another interesting DISTINCT use case is the DISTINCT ON clause. What if you wanted to know who is the most tenured employee in each department? You might write SQL like this:

SELECT *
FROM employee
WHERE (dept,
       hire_date) IN
    (SELECT dept,
            MIN(hire_date)
     FROM employee
     GROUP BY dept)
ORDER BY dept;

+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
|  1 | Joe        | Jones     | Sales       | 1034.23 | 2015-01-15 |
|  2 | Sally      | Smith     | Sales       | 1195.98 | 2015-01-15 |
+----+------------+-----------+-------------+---------+------------+

The issue is in sales both Joe and Sally have the same hire date. What if you just wanted one result per department? You can use some PostgreSQL analytic and window functions, but there is an easier way. PostgreSQL has a special nonstandard clause to find the first row in a group, DISTINCT ON.

SELECT DISTINCT ON (dept) *
FROM employee
ORDER BY dept,
         hire_date ASC,
         last_name ASC;
+----+------------+-----------+-------------+---------+------------+
| id | first_name | last_name |    dept     | salary  | hire_date  |
+----+------------+-----------+-------------+---------+------------+
|  4 | Gunner     | Greenburg | Engineering | 2169.32 | 2017-09-22 |
|  5 | Jack       | Doe       | Finance     | 1945.42 | 2018-06-20 |
|  1 | Joe        | Jones     | Sales       | 1034.23 | 2015-01-15 |
+----+------------+-----------+-------------+---------+------------+

This will return only 1 row per department. The employee with the earliest hire date will be displayed. In the case of duplicates, we are sorting on the last name.

Next: Select ORDER BY