DISTINCT ON for keeping one row per duplicate group

DISTINCT ON keeps exactly one row per group of duplicates, with the sort order you specify deciding which row survives.

About 5–10 minutes · no sign-up needed

Example

Keeping One Retail Order per Customer with DISTINCT ON

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

Result:

customer_id	order_id	order_date	revenue
C101	O2002	2024-03-05	134.5
C102	O2004	2024-03-08	210.75
C103	O2005	2024-03-03	60.0

DISTINCT removes duplicate rows so each value appears once.

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date, revenue

FROM tells SQL which table to pull the data from.

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date, revenue
FROM orders

ORDER BY puts the rows in your chosen order.

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

Try each one in your head first, then open the answer.

1. Using customers, write a query that keeps only one row per phone - the row with the highest customer_id.

SELECT DISTINCT ON (phone) * FROM customers ORDER BY phone, customer_id DESC

2. Using products, write a query that keeps only one row per category - the row with the highest price.

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

3. Using orders, write a query that keeps only one row per order_id - the row with the highest revenue.

SELECT DISTINCT ON (order_id) * FROM orders ORDER BY order_id, revenue DESC

4. Using inventory, write a query that keeps only one row per product_id - the row with the highest stock_count.

SELECT DISTINCT ON (product_id) * FROM inventory ORDER BY product_id, stock_count DESC

5. Using reviews, write a query that keeps only one row per customer_id - the row with the highest rating.

SELECT DISTINCT ON (customer_id) * FROM reviews ORDER BY customer_id, rating DESC

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed