PostgreSQL – DISTINCT ON Clause

The DISTINCT ON clause is used in a SELECT statement to retrieve distinct rows based on the values in specific columns. It allows you to return only the first row for each distinct value combination in the specified columns, effectively removing duplicate combinations.

The basic syntax of using the DISTINCT ON clause is as follows:

SELECT DISTINCT ON (column1, column2, ...) column1, column2, ... FROM table_name WHERE condition ORDER BY column1, column2, ...;

Here’s what the different parts of the DISTINCT ON clause mean:

• DISTINCT ON (column1, column2, ...): This specifies the columns that determine the uniqueness of the rows. The combination of values in these columns is used to identify distinct rows.
• column1, column2, ...: The columns you want to retrieve data from, including the ones specified in the DISTINCT ON clause.
• table_name: The name of the table from which you want to retrieve data.
• WHERE condition: An optional condition that filters the rows based on specified criteria before applying the DISTINCT ON operation.
• ORDER BY column1, column2, ...: This clause specifies the order in which rows are considered for the DISTINCT ON operation. Typically, you order the rows by the same columns used in the DISTINCT ON clause.

Here’s an example of how you might use the DISTINCT ON clause in a PostgreSQL query:

SELECT DISTINCT ON (department_id) department_id, last_name, first_name FROM employees ORDER BY department_id, last_name, first_name;

In this example, we’re retrieving the first row encountered for each distinct department_id. The rows are ordered by department_id first, and then by last_name and first_name.

The DISTINCT ON clause is particularly useful when you want to retrieve distinct rows based on specific criteria, considering only the first occurrence of each distinct value combination. It’s a feature specific to PostgreSQL and is not part of the standard SQL specification.

As always, the behavior of the DISTINCT ON clause might vary depending on the version of PostgreSQL you are using, but the basic functionality should remain consistent.