DISTINCT ON for unique per group in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this DISTINCT ON query?

Given the table employees with columns department, employee_id, and salary, what will this query return?

SELECT DISTINCT ON (department) department, employee_id, salary
FROM employees
ORDER BY department, salary DESC;

PostgreSQL

CREATE TABLE employees (department TEXT, employee_id INT, salary INT);
INSERT INTO employees VALUES
('Sales', 1, 5000),
('Sales', 2, 7000),
('HR', 3, 6000),
('HR', 4, 5500),
('IT', 5, 8000);

SELECT DISTINCT ON (department) department, employee_id, salary
FROM employees
ORDER BY department, salary DESC;

A Returns all employees ordered by department and salary descending

B Returns one row per department with the highest salary employee

C Returns one row per department with the lowest salary employee

D Returns an error because DISTINCT ON requires all ORDER BY columns in SELECT

💡 Hint

Remember that DISTINCT ON (column) returns the first row of each group based on the ORDER BY clause.

The query uses DISTINCT ON (department) to pick one row per department. The ORDER BY department, salary DESC ensures the highest salary per department is first, so that row is returned.

🧠 Conceptual

intermediate

Why must the ORDER BY clause include the DISTINCT ON columns first?

In PostgreSQL, when using DISTINCT ON (column), why is it required that the ORDER BY clause starts with the same column(s) as in DISTINCT ON?

A Because ORDER BY must always sort by all columns in the SELECT clause

B Because ORDER BY and DISTINCT ON are unrelated and can be in any order

C Because PostgreSQL uses the ORDER BY to determine which row to keep per group, and it must group by the DISTINCT ON columns first

D Because DISTINCT ON automatically sorts the result ignoring ORDER BY

💡 Hint

Think about how PostgreSQL picks the first row per group.

PostgreSQL uses the ORDER BY clause to decide which row is first in each group defined by DISTINCT ON. The ORDER BY must start with the DISTINCT ON columns to group rows correctly.

📝 Syntax

advanced

Which query correctly uses DISTINCT ON to get the earliest order per customer?

Given a table orders with columns customer_id, order_id, and order_date, which query returns the earliest order for each customer?

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

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

C SELECT DISTINCT customer_id, order_id, order_date FROM orders ORDER BY customer_id, order_date ASC;

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

💡 Hint

Remember the order of columns in ORDER BY must start with DISTINCT ON columns.

Option D correctly uses DISTINCT ON (customer_id) and orders by customer_id then order_date ascending to get the earliest order per customer.

Option D orders by order_date first, which breaks the DISTINCT ON requirement.

Option D uses DISTINCT (not DISTINCT ON), which does not group by customer_id.

Option D uses DISTINCT ON (order_date), which is incorrect for grouping by customer.

❓ optimization

advanced

How to optimize a DISTINCT ON query on a large table?

You have a large sales table with columns region, sale_id, and sale_date. You want to get the latest sale per region using DISTINCT ON. Which approach improves performance?

A Create an index on (region, sale_date DESC) and run SELECT DISTINCT ON (region) region, sale_id, sale_date FROM sales ORDER BY region, sale_date DESC;

B Create an index on (sale_date DESC) only and run the same query

C Run SELECT DISTINCT ON (region) region, sale_id, sale_date FROM sales ORDER BY sale_date DESC, region;

D Use GROUP BY region and MAX(sale_date) instead of DISTINCT ON

💡 Hint

Think about how indexes can help ORDER BY and DISTINCT ON.

Creating an index on (region, sale_date DESC) matches the DISTINCT ON and ORDER BY columns, allowing PostgreSQL to efficiently find the first row per region.

Option A's index does not include region, so less efficient.

Option A has ORDER BY columns in wrong order, breaking DISTINCT ON.

Option A is a different approach but may be less efficient for returning full rows.

🔧 Debug

expert

Why does this DISTINCT ON query return unexpected rows?

Consider this query:

SELECT DISTINCT ON (category) category, product_id, price
FROM products
ORDER BY price DESC, category;

It is intended to return the most expensive product per category, but it returns unexpected results. What is the problem?

A ORDER BY does not start with the DISTINCT ON column, so grouping is incorrect

B DISTINCT ON requires all columns in ORDER BY to be in SELECT

C DISTINCT ON cannot be used with ORDER BY on multiple columns

D The query is missing a GROUP BY clause

💡 Hint

Check the order of columns in ORDER BY compared to DISTINCT ON.

The ORDER BY clause must start with the DISTINCT ON columns in the same order. Here, ORDER BY starts with price, not category, so PostgreSQL picks the first row by price overall, not per category.

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