DISTINCT
DISTINCT removes duplicate rows from a result.
In this page:
Syntax
SELECT DISTINCT column1, column2
FROM table_name;DISTINCT
SELECT DISTINCT col returns each unique value once, and DISTINCT over several columns returns unique combinations. PostgreSQL also has DISTINCT ON (col), which keeps the first row for each value according to ORDER BY.
DISTINCT can be expensive on large data, so use it deliberately.
Note:
DISTINCT ON is a PostgreSQL extension for picking one row per group.
Example: DISTINCT
CREATE TABLE visits (id INTEGER PRIMARY KEY, city TEXT, browser TEXT);
INSERT INTO visits VALUES (1,'Oslo','chrome'),(2,'Oslo','firefox'),(3,'Rome','chrome'),(4,'Oslo','chrome');
SELECT DISTINCT city FROM visits ORDER BY city;
SELECT DISTINCT city, browser FROM visits ORDER BY city, browser;
SELECT COUNT(DISTINCT city) AS cities FROM visits;
-- Output:
-- city
-- Oslo
-- Rome
-- city | browser
-- Oslo | chrome
-- Oslo | firefox
-- Rome | chrome
-- cities
-- 2Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Adding DISTINCT to hide a join mistake
- Expecting DISTINCT to apply to one column only
- Using it on huge unindexed data
Chapter Summary
- DISTINCT returns unique rows
- Applies to the whole selected row
- DISTINCT ON is PostgreSQL-only
- Use it deliberately
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: