SQLQuerying Data with SELECTbeginner45 min read

Removing Duplicate Results with DISTINCT

Eliminate duplicate rows using SELECT DISTINCT, understand multi-column uniqueness and NULL behavior, explore PostgreSQL's DISTINCT ON extension, and learn how to reason about de-duplication performance.

On This Page (Table of Contents)

In this lesson, you will learn

✓Use SELECT DISTINCT to remove duplicate result rows
✓Understand how DISTINCT evaluates uniqueness across all selected columns
✓Explain how NULL values behave during duplicate elimination
✓Use ORDER BY to request a predictable result order
✓Use PostgreSQL DISTINCT ON to select one row per group
✓Choose a deterministic tie-breaker when selecting a representative row
✓Understand the potential sorting, hashing, and memory costs of de-duplication
✓Recognize when DISTINCT hides a problem caused by an incorrect JOIN

Why Duplicate Results Appear

Tables often contain multiple rows that share some values. Several customers may live in the same city, and many orders may belong to the same customer. A normal SELECT returns a result row for every qualifying source row. DISTINCT removes repeated combinations from the selected result columns; it does not delete or modify records in the underlying table.

See Repeated Cities

sql

1
2
3
SELECT city
FROM customers
ORDER BY city;

Output

The city column may contain the same city multiple times if several customers live there.

SELECT returns values from matching rows. To return each distinct city once, add DISTINCT.

Using SELECT DISTINCT

Place DISTINCT immediately after SELECT. PostgreSQL removes duplicate result rows based on the values of all expressions in the SELECT list. The source table remains unchanged.

Return Unique Cities

sql

1
2
3
SELECT DISTINCT city
FROM customers
ORDER BY city ASC;

Output

One row for each distinct city value, sorted alphabetically according to the applicable collation.

If hundreds of customers live in the same city, that city appears once in the result. ORDER BY requests the output order; DISTINCT by itself does not.

DISTINCT Applies to All Selected Columns

A common misunderstanding is that DISTINCT applies only to the column written first. In reality, it removes rows that are duplicates across the complete selected combination. If either selected value differs, the result rows are distinct.

Unique Department and Job Title Pairs

sql

1
2
3
SELECT DISTINCT department, job_title
FROM employees
ORDER BY department, job_title;

Output

Each distinct department and job_title pair appears once. The same job title may appear more than once when it belongs to different departments.

DISTINCT compares the pair (department, job_title). It does not make department unique independently of job_title.

Compare Single-Column and Multi-Column DISTINCT

Choosing the columns in your SELECT list determines what counts as a duplicate. Compare a query that selects only department with one that selects department and job_title.

Change the Uniqueness Definition

sql

1
2
3
4
5
6
7
8
9
-- Each department appears once
SELECT DISTINCT department
FROM employees
ORDER BY department;
 
-- Each department and job title pair appears once
SELECT DISTINCT department, job_title
FROM employees
ORDER BY department, job_title;

Output

The first query returns unique department values. The second returns unique combinations of department and job_title.

Adding a column changes the definition of a duplicate. Multiple rows for one department may remain in the second query if their job titles differ.

DISTINCT and NULL Values

During duplicate elimination, rows whose corresponding selected values are NULL are treated as duplicates when the remaining selected values also match. Therefore, SELECT DISTINCT city can return a single NULL city value even if many customers have no city recorded. This is different from using city = NULL in a WHERE condition.

Return Unique Cities Including NULL

sql

1
2
3
SELECT DISTINCT city
FROM customers
ORDER BY city NULLS LAST;

Output

Each distinct city appears once, and if any source row has a NULL city, the result contains one NULL row placed last in PostgreSQL.

NULLS LAST controls placement in PostgreSQL. DISTINCT removes repeated NULL values in this result column; it does not fill in missing city values.

DISTINCT Does Not Guarantee Ordering

A database can return distinct rows in different orders depending on the execution plan, data, indexes, or database version. If your application needs a particular order, specify ORDER BY. Do not depend on the apparent order of a result returned without it.

Request a Specific Result Order

sql

1
2
3
SELECT DISTINCT category
FROM products
ORDER BY category ASC;

Output

Distinct category values in ascending order.

DISTINCT defines which rows remain. ORDER BY defines the requested presentation order.

DISTINCT with WHERE

WHERE filters source rows, and DISTINCT removes duplicates from the qualifying selected results. Combining them lets you find unique values among a subset of a table.

Find Categories of Active Products

sql

1
2
3
4
SELECT DISTINCT category
FROM products
WHERE is_active = TRUE
ORDER BY category;

Output

Distinct categories represented by active products.

Inactive products are filtered out before the final distinct result is formed. Categories that appear only on inactive products do not appear.

DISTINCT with Multiple Expressions

The SELECT list can contain expressions as well as column names. DISTINCT evaluates uniqueness using the resulting expression values. This can be useful when normalizing or formatting values, but the transformation determines what counts as equal.

Find Unique Email Normalizations

sql

1
2
3
4
SELECT DISTINCT LOWER(email) AS normalized_email
FROM users
WHERE email IS NOT NULL
ORDER BY normalized_email;

Output

One row per distinct normalized email value among rows with a non-NULL email.

Emails that differ only in case produce the same LOWER(email) value. Whether case-insensitive comparison is appropriate depends on the application's email identity rules.

PostgreSQL DISTINCT ON

PostgreSQL provides DISTINCT ON (expression), a database-specific extension. It keeps the first row from each group of rows sharing the specified DISTINCT ON expression values. ORDER BY determines which row is considered first. The DISTINCT ON expressions must match the leftmost ORDER BY expressions in PostgreSQL.

Select the Latest Order per Customer

sql

1
2
3
4
5
6
7
SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Output

One order per customer: the order with the latest order_date, using the greatest order_id as a tie-breaker when timestamps are equal.

Rows are grouped by customer_id. Within each customer group, the latest timestamp comes first. The extra order_id ordering makes the selection deterministic when two orders have the same timestamp, assuming order_id is unique.

Remember the ORDER BY Rule

In PostgreSQL, DISTINCT ON (customer_id) requires customer_id to be the leftmost ORDER BY expression. Add further columns to control which row is selected within each customer group.

Why Tie-Breakers Matter

If two rows in a DISTINCT ON group share the same ordering values, their relative order is not fully specified. PostgreSQL may choose either row as the first row. Add a unique or otherwise sufficient tie-breaker to define the intended choice. A timestamp alone may not be unique.

Define a Deterministic Latest-Row Choice

sql

1
2
3
4
5
6
SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Output

One row per customer, with order_id providing an additional ordering rule when order_date values tie.

The result is predictable when the ordering columns uniquely determine which row comes first within each group. Make sure the tie-breaker matches the business rule.

DISTINCT ON vs. DISTINCT

These two PostgreSQL query forms solve different problems. DISTINCT removes repeated combinations of all selected expressions. DISTINCT ON chooses one row from each group defined by specific expressions, allowing the query to return additional columns from the chosen row.

Standard DISTINCT

sql

1
2
3
SELECT DISTINCT customer_id
FROM orders
ORDER BY customer_id;

Output

Every customer_id represented in orders appears once.

This query returns unique values, not the details of a particular order for each customer.

Choosing the Right Form

Requirement	Approach	Reason
List each city once	SELECT DISTINCT city	Uniqueness is based on city
List each department and title pair once	SELECT DISTINCT department, job_title	Uniqueness is based on both values
Return the latest order for each customer	SELECT DISTINCT ON (customer_id) ... ORDER BY ...	Selects one ordered row per customer group in PostgreSQL
Return every row tied for the maximum timestamp	Use a window function or join to the maximum timestamp	DISTINCT ON returns one chosen row per group, not every tied row

Alternative: Use ROW_NUMBER to Select One Row per Group

Window functions provide a more portable approach to selecting one row per group. ROW_NUMBER assigns a number within each group according to an ordering rule. Filtering for row number 1 selects one representative row. The tie-breaker should be sufficient to make the ordering deterministic when needed.

Latest Order per Customer with ROW_NUMBER

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT customer_id, order_id, order_date, total_amount
FROM (
  SELECT
    customer_id,
    order_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC, order_id DESC
    ) AS row_num
  FROM orders
) AS ranked_orders
WHERE row_num = 1
ORDER BY customer_id;

Output

One latest order per customer, with order_id as the tie-breaker.

ROW_NUMBER is available in modern PostgreSQL and many other relational databases. The outer query filters the numbered rows because the window result is calculated in the inner query.

DISTINCT and Aggregate Functions

DISTINCT can also appear inside certain aggregate functions. For example, COUNT(DISTINCT customer_id) counts unique non-NULL customer IDs. This is different from applying SELECT DISTINCT to the entire result row. Be explicit about whether you want unique rows or a count of unique values.

Count Unique Customers with Orders

sql

1
2
SELECT COUNT(DISTINCT customer_id) AS unique_customer_count
FROM orders;

Output

One row containing the number of distinct non-NULL customer_id values in orders.

COUNT(DISTINCT expression) ignores NULL expression values. The result is a count, not a list of customer IDs.

Performance: Sorting, Hashing, and Indexes

Duplicate elimination requires the database to identify repeated result combinations. Depending on the query and execution plan, PostgreSQL may use sorting followed by duplicate elimination, hashing, or another strategy. These operations have CPU and memory costs, and large operations may use temporary disk space. An index can sometimes help, but DISTINCT is not guaranteed to use one or to be faster with one.

Inspect DISTINCT Query Planning

sql

1
2
3
EXPLAIN
SELECT DISTINCT category
FROM products;

Output

A planned execution strategy that may include a sort and unique operation, a hash aggregate, or another suitable plan.

EXPLAIN shows the chosen plan. EXPLAIN ANALYZE executes the query and reports actual details, so use it carefully on expensive queries or production systems.

Do Not Use DISTINCT to Hide an Incorrect JOIN

A join may produce multiple rows per entity when a one-to-many relationship is involved. Those rows are not necessarily duplicates: they may represent different related records. If the query produces unexpected duplicates, first inspect the join condition and relationship cardinality. Adding DISTINCT can hide an incorrect join or impose extra work without fixing the underlying issue.

Prefer the Correct Relationship

sql

1
2
3
4
5
6
7
8
9
-- If you only need customers who have an order,
-- EXISTS expresses the existence requirement directly.
SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

Output

Each qualifying customer is returned once, assuming customer_id identifies one customer row.

EXISTS checks whether at least one matching order exists without multiplying customer rows by the number of matching orders. Use a join instead when you need columns from the related rows.

MCQ 1: Basic DISTINCT

Test your understanding of duplicate elimination.

MCQ 2: Multiple Columns

Check which columns determine uniqueness.

MCQ 3: Ordering

Check your understanding of output ordering.

MCQ 4: NULL Values

Review NULL behavior during de-duplication.

MCQ 5: PostgreSQL DISTINCT ON

Review PostgreSQL's DISTINCT ON extension.

MCQ 6: Deterministic Selection

Check why a tie-breaker may be needed.

MCQ 7: Performance

Review how duplicate elimination is implemented.

MCQ 8: JOIN Duplicates

Review a common source of unexpected duplicate results.

Guided Practice: Unique Product Categories

Assume products has product_id, category, price, and is_active. Write a query that returns every category represented by active products, once per category, in alphabetical order. Do not modify the table.

Practice Requirements

sql

1
2
3
4
-- Return the category column only.
-- Include active products.
-- Remove repeated categories.
-- Sort the result alphabetically.

Output

A sorted list of unique categories found among active products. The actual values depend on the table contents.

Use WHERE to filter the source rows, DISTINCT to remove repeated categories, and ORDER BY to specify the output order.

Guided Practice Solution: Unique Product Categories

Compare your query with this solution. Each clause has one clear responsibility.

Unique Categories Solution

sql

1
2
3
4
SELECT DISTINCT category
FROM products
WHERE is_active = TRUE
ORDER BY category ASC;

Output

Each category found among active products appears once, sorted ascending.

WHERE filters rows, DISTINCT removes repeated category values, and ORDER BY controls the requested ordering.

Guided Practice: Latest Order per Customer

Assume order_id uniquely identifies an order and orders has customer_id, order_id, order_date, and total_amount. Write a PostgreSQL query that returns one latest order for every customer represented in orders. If timestamps tie, choose the greatest order_id.

Practice Requirements

sql

1
2
3
4
-- Return customer_id, order_id, order_date, total_amount.
-- Return one order per customer.
-- Prefer the latest order_date.
-- Use order_id DESC to break timestamp ties.

Output

One selected order for each customer_id present in orders.

Use PostgreSQL DISTINCT ON and ensure customer_id is the leftmost ORDER BY expression.

Guided Practice Solution: Latest Order per Customer

This solution uses the timestamp to identify the latest order and order_id as the tie-breaker.

Latest Order Solution

sql

1
2
3
4
5
6
7
SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Output

One order per customer, with the latest timestamp selected and the greatest order_id used when timestamps tie.

The query uses PostgreSQL-specific DISTINCT ON. Customers without any orders cannot appear because this query reads from orders.

Interview Questions and Answers

Review these questions to strengthen your understanding of result uniqueness, PostgreSQL-specific syntax, and query performance.

Lesson Summary

SELECT DISTINCT removes duplicate selected rows without changing the source table. Uniqueness is defined by all selected expressions, and NULL values are treated as duplicates during de-duplication. DISTINCT does not guarantee ordering, so use ORDER BY when needed. PostgreSQL DISTINCT ON selects one row per specified group according to ORDER BY, and a sufficient tie-breaker makes the selection predictable. For portable one-row-per-group queries, consider ROW_NUMBER. Because de-duplication can cost resources, inspect the plan and fix incorrect joins rather than automatically adding DISTINCT.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming DISTINCT applies only to the first selected column

Why it happens: The keyword appears before the SELECT list, which can make it seem like it applies only to the next expression.

Incorrect Approach

sql

SELECT DISTINCT department, job_title FROM employees; -- expected unique departments only

Correct Solution

sql

SELECT DISTINCT department FROM employees;

Key takeaway: DISTINCT applies to the entire selected combination. To return unique departments only, select department alone.

#2 Expecting DISTINCT to sort results automatically

Why it happens: A particular execution plan may happen to produce rows in sorted order.

Incorrect Approach

sql

SELECT DISTINCT category FROM products;

Correct Solution

sql

SELECT DISTINCT category
FROM products
ORDER BY category ASC;

Key takeaway: Only ORDER BY expresses the requested result ordering.

#3 Using DISTINCT ON without a suitable ORDER BY

Why it happens: It is easy to assume the database will automatically choose the newest or best row.

Incorrect Approach

sql

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id;

Correct Solution

sql

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

Key takeaway: The corrected query specifies the latest timestamp first and a tie-breaker second. Adjust the rule to match the actual business requirement.

#4 Assuming DISTINCT ON is portable SQL

Why it happens: It looks like an ordinary SELECT modifier.

Incorrect Approach

sql

SELECT DISTINCT ON (customer_id) customer_id, order_id FROM orders;

Correct Solution

sql

SELECT customer_id, order_id
FROM (
  SELECT customer_id, order_id,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS row_num
  FROM orders
) AS ranked
WHERE row_num = 1;

Key takeaway: DISTINCT ON is a PostgreSQL extension. Window functions are supported by many modern relational databases and offer a more portable approach.

#5 Using DISTINCT to hide an incorrect JOIN

Why it happens: Removing duplicates from the output can appear to fix a query without addressing why rows multiplied.

Incorrect Approach

sql

SELECT DISTINCT c.customer_id, c.full_name
FROM customers AS c
JOIN orders AS o ON TRUE;

Correct Solution

sql

SELECT c.customer_id, c.full_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

Key takeaway: The incorrect join condition matches customers to every order. EXISTS expresses the requirement to find customers who have at least one matching order.

#6 Assuming DISTINCT removes duplicate records from the table

Why it happens: The word 'remove' is confused with deleting stored data.

Incorrect Approach

sql

SELECT DISTINCT city FROM customers; -- assumed to delete duplicates from customers

Correct Solution

sql

SELECT DISTINCT city FROM customers;

Key takeaway: DISTINCT changes only the query result. Removing stored records requires a separate data-modification operation and careful identification of the records to keep.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Removing Duplicate Results with DISTINCT Knowledge Check

Test your understanding with immediate feedback

Question 1 of 8

What does SELECT DISTINCT city return?

Question 2 of 8

What determines duplicates in SELECT DISTINCT department, job_title FROM employees?

Question 3 of 8

Does SELECT DISTINCT category FROM products guarantee alphabetical order without ORDER BY?

Question 4 of 8

If many rows have NULL in the only selected column, how many NULL values can SELECT DISTINCT return?

Question 5 of 8

What does DISTINCT ON (customer_id) select?

Question 6 of 8

Two orders for one customer have the same order_date. What helps make DISTINCT ON selection predictable?

Question 7 of 8

Which statement about DISTINCT performance is correct?

Question 8 of 8

A join returns more rows than expected. What should you do first?

Technical and placement interview questions

Frequently tested interview questions with detailed, full answers (click to expand)

Q1:What does DISTINCT do?

Answer:

DISTINCT removes duplicate result rows based on the complete combination of selected expressions. It does not modify the source table.

Q2:Does DISTINCT apply only to the first selected column?

Answer:

No. It applies to the complete selected row. For SELECT DISTINCT city, state, the city-state pair determines uniqueness.

Q3:Does DISTINCT guarantee output ordering?

Answer:

No. Use ORDER BY whenever the output must follow a particular order.

Q4:How does DISTINCT handle NULL values?

Answer:

During duplicate elimination, rows with corresponding NULL values are treated as duplicates when their other selected values also match.

Q5:What is DISTINCT ON?

Answer:

DISTINCT ON is a PostgreSQL extension that selects the first row from each group defined by specified expressions, according to ORDER BY.

Q6:Is DISTINCT ON standard SQL?

Answer:

No. It is PostgreSQL-specific. ROW_NUMBER with a partition and an ordering rule is a more portable alternative.

Q7:Why does DISTINCT ON need ORDER BY?

Answer:

ORDER BY determines which row appears first in each group. Without a suitable ordering rule, the selected row may be unpredictable.

Q8:What is a tie-breaker?

Answer:

A tie-breaker is an additional ordering expression that resolves cases where the primary ordering values are equal, such as using a unique order_id after order_date.

Q9:What is the difference between DISTINCT and DISTINCT ON?

Answer:

DISTINCT removes duplicate combinations across all selected expressions. DISTINCT ON selects one ordered row per group based on specified expressions.

Q10:Can DISTINCT be used with WHERE?

Answer:

Yes. WHERE filters qualifying source rows, and DISTINCT removes duplicate selected results among those rows.

Q11:What does COUNT(DISTINCT customer_id) do?

Answer:

It counts distinct non-NULL customer_id values rather than returning a list of unique customer IDs.

Q12:How can DISTINCT affect performance?

Answer:

The database must identify duplicate combinations. Depending on the plan, this may require sorting, hashing, memory, CPU, or temporary disk space.

Q13:Does DISTINCT always require sorting?

Answer:

No. PostgreSQL can use different execution strategies, including hash-based de-duplication where suitable.

Q14:Should DISTINCT be used to fix every duplicate caused by a JOIN?

Answer:

No. First inspect the join condition and relationship cardinality. DISTINCT may hide an incorrect join or remove rows that are meaningful.

Q15:How do you inspect a DISTINCT query plan?

Answer:

Use EXPLAIN to see the planned execution strategy. EXPLAIN ANALYZE executes the query and reports actual execution details.

Q16:Can an index make DISTINCT faster?

Answer:

Sometimes. An index may help provide values in a useful order or support the access path, but the optimizer may choose another strategy.

Key takeaways

DISTINCT applies to the complete combination of selected columns, not only the first column.
Rows that have the same selected values are represented once in a DISTINCT result.
DISTINCT does not guarantee output order; use ORDER BY when order matters.
For duplicate elimination, NULL values in corresponding positions are treated as duplicates of one another.
DISTINCT may require sorting, hashing, or another de-duplication strategy; performance depends on the query and execution plan.
PostgreSQL DISTINCT ON selects the first row in each group according to the required ORDER BY ordering.
For predictable DISTINCT ON results, specify enough ORDER BY columns to break ties when necessary.
DISTINCT ON is a PostgreSQL extension and is not portable to every SQL database.
DISTINCT is not a replacement for correcting an incorrect JOIN condition.
A primary key may already guarantee uniqueness for the selected key; adding DISTINCT is not automatically necessary.

Was this lesson helpful?

Found an error? Report an issue