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:
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:
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:
Important Notes:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4