Often in PostgreSQL you may want to use SELECT DISTINCT to select distinct values from one column while also returning values from other columns.
You can use the following basic syntax to do so:
SELECT DISTINCT ON (team)
team, position, points
FROM athletes;
This particular example will return only distinct values from the team column of the table but it will also return the corresponding values from the position and points columns.
The rows chosen to be returned will be random unless you specify an order using the ORDER BY clause:
SELECT DISTINCT ON (team)
team, position, points
FROM athletes
ORDER BY team, position;
This example will also return only distinct values from the team column but the rows will be chosen based on order by team, then position.
The following example shows how to use this syntax in practice.
Example: How to Use SELECT DISTINCT for Only One Column in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER PRIMARY KEY,
team TEXT,
position TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Center', 40);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0007, 'Hawks', 'Forward', 33);
INSERT INTO athletes VALUES (0008, 'Hawks', 'Center', 12);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | position | points
-----------+-------+----------+--------
1 | Mavs | Guard | 15
2 | Mavs | Guard | 12
3 | Mavs | Forward | 23
4 | Mavs | Center | 40
5 | Hawks | Guard | 29
6 | Hawks | Forward | 14
7 | Hawks | Forward | 33
8 | Hawks | Center | 12
Suppose that we would like to select values from the team, position and points columns but ensure that only distinct values are chosen from the team column.
We can use the following syntax to do so:
SELECT DISTINCT ON (team)
team, position, points
FROM athletes;
team | position | points
-------+----------+--------
Hawks | Center | 12
Mavs | Guard | 15
Notice that the resulting table contains values from the team, position and points columns but only distinct values are selected for the team column.
If you’d like to select the distinct values from the team column based on a specific order then you can use the ORDER BY clause as follows:
SELECT DISTINCT ON (team)
team, position, points
FROM athletes
ORDER BY team, position;
team | position | points
-------+----------+--------
Hawks | Center | 12
Mavs | Center | 40
The resulting table contains distinct values from the team column but the rows are chosen based on order by team, then position.
Feel free to use similar syntax to select distinct values based on one column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to SELECT DISTINCT on Multiple Columns
PostgreSQL: Use COUNT DISTINCT with CASE WHEN
PostgreSQL: How to Count Distinct Over Multiple Columns
