SQL Academy · Lesson

PostgreSQL DISTINCT ON

Pick one row per group.

What Is DISTINCT ON?

PostgreSQL offers a powerful extension to the standard DISTINCT keyword called DISTINCT ON. While regular DISTINCT removes fully duplicate rows, DISTINCT ON lets you pick exactly one row per group based on one or more columns you choose.

Think of it as: 'For each unique value in this column, give me one row.' This is extremely useful when you want the latest order per customer, the highest score per student, or the first event per category.

Basic DISTINCT ON Syntax

The syntax places DISTINCT ON (column) right after SELECT. The column inside the parentheses defines the grouping — PostgreSQL will return one row for each unique value of that column.

The example below returns one row per customer_id from an orders table. PostgreSQL picks which row to return based on the ORDER BY clause that follows.

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

All lessons in this course

← Back to SQL Academy