0Pricing
SQL Academy · Lesson

PostgreSQL DISTINCT ON

Pick one row per group.

PostgreSQL DISTINCT ON is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Setting Up Example Tables

Let us create a simple orders table and insert some sample rows so we can try out DISTINCT ON in action. We have three customers, each with multiple orders on different dates.

CREATE TABLE orders (
  order_id      SERIAL PRIMARY KEY,
  customer_id   INT,
  order_date    DATE,
  total_amount  NUMERIC(10, 2)
);

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
  (1, '2024-01-05', 120.00),
  (1, '2024-03-12', 85.50),
  (1, '2024-06-20', 200.00),
  (2, '2024-02-14', 45.00),
  (2, '2024-05-30', 310.00),
  (3, '2024-04-01', 75.00);

Latest Order Per Customer

A very common use case: find the most recent order for each customer. By ordering order_date DESC within each customer_id group, DISTINCT ON picks the row with the latest date.

Notice the ORDER BY clause must start with the same column(s) listed in DISTINCT ON. This is a PostgreSQL requirement.

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

Earliest Order Per Customer

To get the first (oldest) order per customer instead, simply change the sort direction to ASC. The only difference is the order of rows within each group — DISTINCT ON always picks the first row after sorting.

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

The ORDER BY Rule

Important rule: When using DISTINCT ON (col), the ORDER BY clause must begin with the same column(s) listed inside DISTINCT ON. If it does not, PostgreSQL will raise an error.

After the grouping column(s), you can add any additional sort criteria to control which row within each group is selected.

-- Correct: ORDER BY starts with the DISTINCT ON column
SELECT DISTINCT ON (customer_id)
  customer_id, order_date, total_amount
FROM orders
ORDER BY customer_id, total_amount DESC;

-- This would cause an error:
-- ORDER BY order_date DESC  (missing customer_id at the start)

Highest Score Per Student

Here is another practical example using a test_scores table. We want the highest score each student ever achieved. By sorting score DESC within each student group, DISTINCT ON returns only the top-scoring row per student.

CREATE TABLE test_scores (
  id         SERIAL PRIMARY KEY,
  student_id INT,
  subject    VARCHAR(50),
  score      INT,
  taken_on   DATE
);

INSERT INTO test_scores (student_id, subject, score, taken_on) VALUES
  (101, 'Math',    92, '2024-02-10'),
  (101, 'Math',    78, '2024-04-15'),
  (102, 'Math',    85, '2024-02-10'),
  (102, 'Math',    91, '2024-04-15'),
  (103, 'Math',    67, '2024-02-10');

SELECT DISTINCT ON (student_id)
  student_id, subject, score, taken_on
FROM test_scores
ORDER BY student_id, score DESC;

DISTINCT ON With Multiple Columns

You can group by more than one column by listing multiple columns inside DISTINCT ON. This returns one row for each unique combination of those columns.

The example below picks the highest score per student per subject, treating each (student, subject) pair as its own group.

INSERT INTO test_scores (student_id, subject, score, taken_on) VALUES
  (101, 'Science', 88, '2024-03-01'),
  (101, 'Science', 95, '2024-05-20'),
  (102, 'Science', 72, '2024-03-01');

SELECT DISTINCT ON (student_id, subject)
  student_id, subject, score, taken_on
FROM test_scores
ORDER BY student_id, subject, score DESC;

Filtering With WHERE

DISTINCT ON works naturally alongside WHERE clauses. The filter is applied first, then DISTINCT ON picks one row per group from the filtered results.

Here we find the most recent order per customer, but only for orders above 100.

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

DISTINCT ON vs GROUP BY

Both DISTINCT ON and GROUP BY can produce one row per group, but they serve different purposes:

  • GROUP BY collapses rows and requires aggregate functions (SUM, MAX, etc.) for non-grouped columns.
  • DISTINCT ON keeps an actual existing row — all its columns are available without aggregation.

Use GROUP BY when you need aggregated values. Use DISTINCT ON when you need the full data from a specific row within each group.

-- GROUP BY: only aggregated columns allowed
SELECT customer_id, MAX(order_date) AS latest_date
FROM orders
GROUP BY customer_id;

-- DISTINCT ON: returns the whole row for that latest date
SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

Using DISTINCT ON in a Subquery

Sometimes you need to apply further filtering or sorting on top of the DISTINCT ON result. Because the outer ORDER BY is tied to the grouping column, you can wrap the query in a subquery (or CTE) to apply a different sort on the final output.

This example first picks the latest order per customer, then sorts the final result by total_amount descending.

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

Quick Check

Let us check your understanding of DISTINCT ON. Read the query below carefully and choose the answer that best describes what it returns.

SELECT DISTINCT ON (department_id) department_id, employee_name, salary FROM employees ORDER BY department_id, salary DESC;

Lesson Recap

Great work! Here is a summary of what you learned about PostgreSQL DISTINCT ON:

  • DISTINCT ON (col) returns exactly one row per unique value of the specified column(s).
  • The ORDER BY clause must start with the same column(s) listed in DISTINCT ON — it controls which row is selected from each group.
  • You can use multiple columns: DISTINCT ON (col1, col2) groups by the combination of both.
  • Unlike GROUP BY, DISTINCT ON returns a real row with all its original columns — no aggregation needed.
  • Wrap the query in a subquery when you need to sort the final output by a different column.

DISTINCT ON is a PostgreSQL-specific feature and one of the most elegant ways to solve 'pick one row per group' problems.

Frequently asked questions

Is the “PostgreSQL DISTINCT ON” lesson free?

Yes — the full text of “PostgreSQL DISTINCT ON” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “PostgreSQL DISTINCT ON”?

Pick one row per group. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “PostgreSQL DISTINCT ON” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. SELECT DISTINCT Basics
  2. DISTINCT on Multiple Columns
  3. PostgreSQL DISTINCT ON
  4. Counting Unique Values
← Back to SQL Academy