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.