🌱BeginnerFiltering & Sorting
DISTINCT
Eliminate duplicate rows from query results using SELECT DISTINCT.
Overview
SELECT DISTINCT removes duplicate rows from the result set only one copy of each unique row combination is returned. DISTINCT operates on the entire row by default, but DISTINCT ON (col) (a PostgreSQL extension) returns the first row for each unique value of the specified column(s).
DISTINCT requires PostgreSQL to sort or hash the entire result set to detect duplicates, which can be expensive. If you only need distinct values from one column, COUNT(DISTINCT col) is more efficient than SELECT DISTINCT col. For counting unique values across a large dataset, consider approximate methods like HyperLogLog.
Key Points
- •SELECT DISTINCT removes duplicate rows from results
- •DISTINCT compares all selected columns together
- •DISTINCT ON (col) is PostgreSQL-only: returns first row per unique col value
- •DISTINCT ON must come first in SELECT: SELECT DISTINCT ON (dept) *
- •DISTINCT adds processing overhead avoid on huge result sets unless necessary
Examples
Basic DISTINCT usage
-- All unique cities customers are from
SELECT DISTINCT city FROM customers ORDER BY city;
-- Unique combinations of country + city
SELECT DISTINCT country, city FROM customers ORDER BY country, city;DISTINCT ON PostgreSQL extension
-- Latest order per customer (first row per customer_id when ordered by date desc)
SELECT DISTINCT ON (customer_id)
customer_id, order_date, total
FROM orders
ORDER BY customer_id, order_date DESC;ORDER BY must start with the DISTINCT ON columns.
Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4