DISTINCT ON for unique per group in PostgreSQL

Introduction

To get one unique row per group from a table, picking the first row based on a specific order.

You want to find the latest order for each customer.

You need the first event recorded for each user.

You want one example product from each category.

You want to remove duplicates but keep the earliest entry per group.

Syntax

PostgreSQL

SELECT DISTINCT ON (group_column) group_column, other_columns
FROM table_name
ORDER BY group_column, order_column [ASC|DESC];

DISTINCT ON works only in PostgreSQL.

The ORDER BY must include the same columns as DISTINCT ON first, to define which row to keep.

Examples

Gets the latest order for each customer by ordering dates descending.

PostgreSQL

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

Gets the cheapest product in each category.

PostgreSQL

SELECT DISTINCT ON (category) category, product_name, price
FROM products
ORDER BY category, price ASC;

Sample Program

This creates a table of orders, inserts some sample data, then selects the latest order per customer.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2024-01-01'),
(1, '2024-02-01'),
(2, '2024-01-15'),
(2, '2024-01-10'),
(3, '2024-03-01');

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

Important Notes

Always include ORDER BY with DISTINCT ON to control which row you get per group.

DISTINCT ON returns the first row it finds per group based on ORDER BY.

Summary

DISTINCT ON helps pick one unique row per group in PostgreSQL.

Use ORDER BY to decide which row to keep.

Great for getting latest, earliest, or specific rows per group.

Practice

1. What does the DISTINCT ON clause do in PostgreSQL?

easy

A. It returns one unique row per specified group based on the first row in ORDER BY.

B. It removes all duplicate rows from the entire table.

C. It sorts the entire table without removing duplicates.

D. It groups rows and sums numeric columns automatically.

Solution

1. Step 1: Understand DISTINCT ON purpose

   DISTINCT ON returns only one row per group defined by the columns inside the parentheses.

2. Step 2: Role of ORDER BY with DISTINCT ON

   The row returned per group is the first row according to the ORDER BY clause.

3. Final Answer:

   It returns one unique row per specified group based on the first row in ORDER BY. -> Option A

4. Quick Check:

   DISTINCT ON = unique per group [OK]

Hint: DISTINCT ON picks first row per group using ORDER BY [OK]

Common Mistakes:

• Thinking DISTINCT ON removes all duplicates globally
• Ignoring ORDER BY affects which row is chosen
• Confusing DISTINCT ON with GROUP BY

2. Which of the following is the correct syntax to select the latest order per customer using DISTINCT ON?

easy

A. SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id DESC;

B. SELECT DISTINCT ON customer_id * FROM orders ORDER BY order_date DESC;

C. SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC;

D. SELECT DISTINCT ON (order_date) * FROM orders ORDER BY customer_id DESC;

Solution

1. Step 1: Check DISTINCT ON syntax

   The correct syntax requires parentheses around the column(s) for DISTINCT ON, like DISTINCT ON (customer_id).

2. Step 2: Verify ORDER BY matches DISTINCT ON

   ORDER BY must start with the same columns as DISTINCT ON, here customer_id, then order by order_date DESC to get latest order.

3. Final Answer:

   SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC; -> Option C

4. Quick Check:

   Correct syntax = SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC; [OK]

Hint: Use parentheses with DISTINCT ON and match ORDER BY columns [OK]

Common Mistakes:

• Omitting parentheses around DISTINCT ON columns
• ORDER BY columns not starting with DISTINCT ON columns
• Using wrong columns in DISTINCT ON

3. Given the table sales with columns region, sale_date, and amount, what will this query return?

SELECT DISTINCT ON (region) region, sale_date, amount FROM sales ORDER BY region, sale_date DESC;

medium

A. One row per region with the latest sale_date.

B. One row per region with the earliest sale_date.

C. All rows sorted by region and sale_date descending.

D. An error because ORDER BY does not match DISTINCT ON.

Solution

1. Step 1: Analyze DISTINCT ON and ORDER BY

   DISTINCT ON (region) picks one row per region. ORDER BY region, sale_date DESC orders rows by region and then newest sale_date first.

2. Step 2: Determine which row is chosen per group

   The first row per region after ordering is the one with the latest sale_date, so that row is returned.

3. Final Answer:

   One row per region with the latest sale_date. -> Option A

4. Quick Check:

   Latest per group = One row per region with the latest sale_date. [OK]

Hint: ORDER BY controls which row DISTINCT ON picks per group [OK]

Common Mistakes:

• Assuming earliest date instead of latest
• Thinking all rows are returned
• Believing syntax error due to ORDER BY

4. Identify the error in this query that tries to get the earliest login per user:

SELECT DISTINCT ON (user_id) user_id, login_time FROM logins ORDER BY login_time ASC;

medium

A. login_time cannot be selected with DISTINCT ON.

B. ORDER BY cannot use ASC with DISTINCT ON.

C. DISTINCT ON requires GROUP BY clause.

D. DISTINCT ON columns must match the first columns in ORDER BY.

Solution

1. Step 1: Check DISTINCT ON and ORDER BY columns

   DISTINCT ON (user_id) requires ORDER BY to start with user_id as the first column.

2. Step 2: Identify mismatch in ORDER BY

   ORDER BY only has login_time ASC, missing user_id first, causing error.

3. Final Answer:

   DISTINCT ON columns must match the first columns in ORDER BY. -> Option D

4. Quick Check:

   ORDER BY start matches DISTINCT ON [OK]

Hint: ORDER BY must start with DISTINCT ON columns [OK]

Common Mistakes:

• Ignoring ORDER BY column order
• Adding unnecessary GROUP BY
• Thinking ASC/DESC causes error

5. You have a table messages with columns chat_id, message_id, and sent_at. You want to get the most recent message per chat. Which query correctly achieves this?

hard

A. SELECT DISTINCT ON (message_id) chat_id, message_id, sent_at FROM messages ORDER BY sent_at DESC;

B. SELECT DISTINCT ON (chat_id) chat_id, message_id, sent_at FROM messages ORDER BY chat_id, sent_at DESC;

C. SELECT chat_id, MAX(sent_at) FROM messages GROUP BY chat_id;

D. SELECT chat_id, message_id, sent_at FROM messages WHERE sent_at = MAX(sent_at) GROUP BY chat_id;

Solution

1. Step 1: Understand requirement for most recent message per chat

   We want one row per chat_id with the latest sent_at.

2. Step 2: Check each option

   SELECT DISTINCT ON (chat_id) chat_id, message_id, sent_at FROM messages ORDER BY chat_id, sent_at DESC; uses DISTINCT ON (chat_id) with ORDER BY chat_id, sent_at DESC, which returns the latest message per chat. SELECT DISTINCT ON (message_id) chat_id, message_id, sent_at FROM messages ORDER BY sent_at DESC; uses DISTINCT ON (message_id), which is unique per message, not per chat. SELECT chat_id, MAX(sent_at) FROM messages GROUP BY chat_id; returns only chat_id and max sent_at, missing message_id. SELECT chat_id, message_id, sent_at FROM messages WHERE sent_at = MAX(sent_at) GROUP BY chat_id; has invalid syntax mixing WHERE and GROUP BY incorrectly.

3. Final Answer:

   SELECT DISTINCT ON (chat_id) chat_id, message_id, sent_at FROM messages ORDER BY chat_id, sent_at DESC; -> Option B

4. Quick Check:

   Latest per group with DISTINCT ON = SELECT DISTINCT ON (chat_id) chat_id, message_id, sent_at FROM messages ORDER BY chat_id, sent_at DESC; [OK]

Hint: DISTINCT ON with ORDER BY picks latest per group easily [OK]

Common Mistakes:

• Using DISTINCT ON on wrong column
• Trying GROUP BY without selecting all columns
• Incorrect WHERE with aggregate functions