Using SELECT DISTINCT on Multiple Columns in PostgreSQL

How to SELECT DISTINCT on Multiple Columns in PostgreSQL?

In PostgreSQL, you can use the DISTINCT clause to retrieve unique rows based on one or more columns. By specifying multiple columns with DISTINCT, you filter the results to return only the unique combinations of the specified columns. Additionally, PostgreSQL provides a DISTINCT ON clause that allows even more control by selecting the first row of each unique group.

Using DISTINCT on Multiple Columns

Using DISTINCT with multiple columns removes duplicate rows based on the unique combination of those columns.

Syntax:

-- Basic syntax for selecting distinct combinations of columns
SELECT DISTINCT column1, column2, ...
FROM table_name;

Using DISTINCT ON for More Control

With DISTINCT ON, you can specify which rows to return when there are duplicate values across multiple columns, particularly helpful when combined with ORDER BY.

Syntax:

-- Syntax for DISTINCT ON with control over row selection
SELECT DISTINCT ON (column1, column2, ...) column1, column2, ...
FROM table_name
ORDER BY column1, column2, ..., additional_column;

Explanation:

• DISTINCT ON (column1, column2, ...): Ensures that each unique combination of column1 and column2 appears only once.
• ORDER BY: Defines the order for selecting rows in cases where there are duplicate values for the specified columns.

Examples:

1. Basic Use of DISTINCT on Multiple Columns

Code:

-- Retrieve unique combinations of first_name and last_name
SELECT DISTINCT first_name, last_name -- Get distinct pairs of first and last names
FROM employees; -- From the employees table

Explanation:

• DISTINCT first_name, last_name: Returns only unique combinations of first_name and last_name.

2. Using DISTINCT ON to Select the First Row for Each Unique Combination

Code:

-- Retrieve unique combinations of department and role, showing the highest salary
SELECT DISTINCT ON (department, role) department, role, salary
FROM employees
ORDER BY department, role, salary DESC; -- Order to get highest salary per unique combination

Explanation:

• DISTINCT ON (department, role): Ensures each unique combination of department and role appears once.
• ORDER BY department, role, salary DESC: Selects the row with the highest salary within each unique department and role combination.

Important Notes:

• Efficiency: When using DISTINCT ON, an ORDER BY clause is required, and it should include all columns specified in DISTINCT ON to control the returned rows.
• Best Use Cases: DISTINCT ON is particularly helpful when needing to select specific rows within unique groups, such as the highest or latest value.