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.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/select-distinct-multiple-columns-postgresql.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics