Topic 13 of 52
SELECT DISTINCT
Overview
SELECT DISTINCT eliminates duplicate rows from the result set, returning only unique combinations of the selected columns. It's used when you need a unique list without aggregation.
Syntax
sql
-- DISTINCT on single column
SELECT DISTINCT city FROM customers;
-- DISTINCT on multiple columns (unique COMBINATION)
SELECT DISTINCT city, country FROM customers;
-- Returns unique city+country pairs, not unique cities alone
-- Count distinct values
SELECT COUNT(DISTINCT user_id) AS unique_buyers FROM orders;
-- PostgreSQL: DISTINCT ON (keep first row per group)
SELECT DISTINCT ON (user_id)
user_id, order_id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC; -- keeps the LATEST order per user
-- DISTINCT vs GROUP BY (same result, different performance)
SELECT DISTINCT category FROM products;
-- equivalent to:
SELECT category FROM products GROUP BY category;Common Pitfalls
- DISTINCT applies to the entire row (all columns) — SELECT DISTINCT name, email returns unique name+email combinations, not unique names.
- DISTINCT can be slow on large tables — it requires sorting. Use GROUP BY with appropriate indexes for better performance.
- Interview tip: COUNT(DISTINCT column) counts unique non-NULL values — if you need to count NULLs too, use COUNT(*) with CASE.
Real-World Example
Finding unique visitors and their most recent session:
example
sql
-- Unique countries of customers who ordered in 2025
SELECT DISTINCT
c.country,
c.city
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
ORDER BY c.country, c.city;
-- Get each user's most recent purchase (PostgreSQL DISTINCT ON)
SELECT DISTINCT ON (o.user_id)
o.user_id,
u.name,
o.id AS last_order_id,
o.total_amount,
o.created_at AS last_purchase
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
ORDER BY o.user_id, o.created_at DESC;
-- How many unique products were ever ordered?
SELECT COUNT(DISTINCT product_id) AS unique_products_sold
FROM order_items;