DISTINCT ON for unique per group in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does DISTINCT ON do in PostgreSQL?

DISTINCT ON returns the first row of each unique group based on specified columns.

It helps to get one unique row per group.

beginner

How do you specify which row to keep when using DISTINCT ON?

You use ORDER BY to control which row appears first in each group.

The first row in the order is kept.

intermediate

Write a simple query to get one unique row per category from a products table using DISTINCT ON.

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

This returns the most expensive product per category.

intermediate

Why is ORDER BY important with DISTINCT ON?

Because DISTINCT ON keeps the first row of each group, ORDER BY decides which row is first.

Without proper ordering, you might get unexpected rows.

advanced

Can DISTINCT ON be used without ORDER BY in PostgreSQL?

No, DISTINCT ON requires ORDER BY to define which row to keep.

PostgreSQL will raise an error if ORDER BY does not include the DISTINCT ON columns at the start.

What does DISTINCT ON (column) return in PostgreSQL?

A Only rows with duplicate values in the column

B All rows with unique values in the column

C The first row for each unique value in the column

D All rows sorted by the column

DISTINCT ON returns the first row for each unique value in the specified column(s).

Which clause must be used with DISTINCT ON to control which row is returned?

A GROUP BY

B ORDER BY

C WHERE

D HAVING

ORDER BY controls which row is first and thus returned by DISTINCT ON.

What happens if ORDER BY does not include the DISTINCT ON columns?

A PostgreSQL raises an error

B Query runs normally

C Duplicates are removed anyway

D Rows are randomly returned

PostgreSQL requires ORDER BY to start with the DISTINCT ON columns; otherwise, it raises an error.

How can you get the most recent order per customer using DISTINCT ON?

A Use <code>ORDER BY order_date DESC</code> only

B Use <code>DISTINCT ON (order_date)</code> and order by <code>order_date DESC</code>

C Use <code>GROUP BY customer_id</code> only

D Use <code>DISTINCT ON (customer_id)</code> and order by <code>customer_id, order_date DESC</code>

Ordering by customer_id, order_date DESC ensures the latest order per customer is returned.

Which of these is a correct use of DISTINCT ON?

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

B SELECT DISTINCT ON (category) * FROM products;

C SELECT DISTINCT ON (category) * FROM products ORDER BY price DESC;

D SELECT DISTINCT ON * FROM products ORDER BY category;

DISTINCT ON columns must appear first in ORDER BY. Option A is correct.

Explain how DISTINCT ON works in PostgreSQL and why ORDER BY is important.

Think about picking one favorite item per group.

Describe a real-life scenario where DISTINCT ON is useful and how you would write the query.

Imagine you want one special record per group.