SQL SELECT DISTINCT on Multiple Columns: How It Works (with Examples)

SELECT DISTINCT removes duplicate rows from a query result. When you list more than one column, many people expect it to work column by column, but it does not: DISTINCT looks at the combination of all the listed columns and returns each unique combination once. This guide explains how that works with an example, how to count distinct combinations, and how to return one full row per value when DISTINCT is not enough. The syntax works in MySQL, SQL Server, PostgreSQL, SQLite and Oracle unless noted.

Quick answer

SELECT DISTINCT city, state
FROM customers;

This returns every unique pair of city and state. A city that appears in two different states appears twice, once for each state.

How DISTINCT works with multiple columns

Imagine a customers table with these rows:

• Springfield, IL
• Springfield, IL
• Springfield, MO
• Portland, OR
• Portland, ME
• Portland, OR

SELECT DISTINCT city FROM customers returns two rows: Springfield and Portland.

SELECT DISTINCT city, state FROM customers returns four rows: Springfield IL, Springfield MO, Portland OR and Portland ME. Duplicates are removed only where both city and state match.

The DISTINCT keyword goes once, right after SELECT. Writing SELECT DISTINCT city, DISTINCT state is a syntax error; one DISTINCT always applies to the whole row.

The same result with GROUP BY

SELECT city, state
FROM customers
GROUP BY city, state;

GROUP BY returns the same unique combinations. Use it when you also want an aggregate for each combination, such as how many customers are in each:

SELECT city, state, COUNT(*) AS customers
FROM customers
GROUP BY city, state
ORDER BY customers DESC;

Count distinct combinations

To count how many unique combinations exist, wrap the DISTINCT query in a subquery. This works in every major database:

SELECT COUNT(*) AS unique_locations
FROM (SELECT DISTINCT city, state FROM customers) AS t;

MySQL also accepts COUNT(DISTINCT city, state) directly, but SQL Server does not, so the subquery is the portable choice. For a single column, COUNT(DISTINCT city) works everywhere.

Return one full row per value

Sometimes you want one complete row for each city, for example the most recent customer. DISTINCT cannot do this, because adding more columns changes which rows count as duplicates.

PostgreSQL: DISTINCT ON

SELECT DISTINCT ON (city) city, state, name, created_at
FROM customers
ORDER BY city, created_at DESC;

This returns the first row for each city according to the ORDER BY.

Any modern database: ROW_NUMBER()

SELECT city, state, name, created_at
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY created_at DESC) AS rn
  FROM customers
) AS t
WHERE rn = 1;

This works in SQL Server, MySQL 8+, PostgreSQL, SQLite 3.25+ and Oracle.

Things to know

• NULLs: DISTINCT treats NULL values as equal to each other, so rows with NULL in the same columns collapse into one.
• Case sensitivity: whether “springfield” and “Springfield” count as the same depends on the column’s collation.
• ORDER BY: with DISTINCT, columns in ORDER BY generally need to appear in the SELECT list.
• Performance: DISTINCT has to sort or hash the results. On large tables, an index on the selected columns helps. If you are getting duplicates because of a JOIN, fixing the JOIN is often better than adding DISTINCT.

Common mistakes

• Expecting DISTINCT on one column only. If you add an ID or timestamp column to the SELECT list, almost every row becomes unique and DISTINCT appears to do nothing. Select only the columns that define a duplicate.
• Using parentheses as if DISTINCT were a function. SELECT DISTINCT(city), state is still DISTINCT on both columns; the parentheses just wrap the first column.
• Hiding a JOIN problem. Duplicate rows after joining tables usually mean the join matches more rows than expected. Check the join conditions before reaching for DISTINCT.
• Counting the wrong thing. COUNT(DISTINCT city) counts unique cities, not unique city and state pairs.

Frequently asked questions

Is DISTINCT the same as UNIQUE?

In queries, Oracle accepts SELECT UNIQUE as a synonym for SELECT DISTINCT, but DISTINCT is the standard and works everywhere. UNIQUE is more commonly used as a constraint that prevents duplicate values from being stored in the first place.

Is DISTINCT or GROUP BY faster?

For plain lists of unique combinations, most databases create the same execution plan for both, so performance is usually identical. Choose whichever reads more clearly.

Practice setup

To try these queries locally, install a database such as MySQL on Windows 11 or SQL Server on Windows 10. If you need to clean up a test database afterwards, see how to drop a SQL database that is in use.

Summary

1. SELECT DISTINCT col1, col2 returns unique combinations of all listed columns.
2. GROUP BY col1, col2 does the same and allows aggregates.
3. Count combinations with COUNT(*) over a DISTINCT subquery.
4. Use DISTINCT ON (PostgreSQL) or ROW_NUMBER() to get one full row per value.