DISTINCT on Multiple Columns
Uniqueness across combinations.
Why Multiple Columns?
You already know that SELECT DISTINCT removes duplicate rows. But what does "duplicate" mean when your query returns several columns?
A row is only considered a duplicate when every selected column has the same value as another row. If even one column differs, both rows are kept.
Sample Table: Orders
Let's create a small orders table to experiment with. It stores which customer bought from which category and in which country.
CREATE TABLE orders (
id INTEGER,
customer VARCHAR(50),
category VARCHAR(50),
country VARCHAR(50)
);
INSERT INTO orders VALUES
(1, 'Alice', 'Electronics', 'US'),
(2, 'Alice', 'Books', 'US'),
(3, 'Bob', 'Electronics', 'UK'),
(4, 'Alice', 'Electronics', 'US'),
(5, 'Bob', 'Electronics', 'UK'),
(6, 'Carol', 'Clothing', 'CA');All lessons in this course
- SELECT DISTINCT Basics
- DISTINCT on Multiple Columns
- PostgreSQL DISTINCT ON
- Counting Unique Values