Get the Latest Row per Group With DISTINCT ON

"The latest order per customer" is one of the most common questions in SQL and one of the most often written badly. People join the table to a subquery that finds the maximum date, and then get two rows when two orders share the date. PostgreSQL has a short form for this, DISTINCT ON. This page shows it, its one rule, and the standard version for databases that do not have it.

Step 1: DISTINCT ON

DISTINCT ON (customer_id) keeps the first row for each customer, where "first" is decided by the ORDER BY.

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

 customer_id | order_id | order_date | status
-------------+----------+------------+--------
           1 |       11 | 2026-09-20 | new
           2 |       10 | 2026-09-14 | new
           3 |        6 | 2026-09-02 | paid
           5 |        8 | 2026-09-10 | new
           6 |        9 | 2026-09-12 | paid
           8 |       12 | 2026-09-25 | paid
(6 rows)

One row per customer who has orders, and each is the most recent. The ORDER BY does two jobs. It groups the rows by customer, and inside each customer it puts the newest first, so the newest is the one that is kept.

The rule

The DISTINCT ON expressions must be the first expressions of the ORDER BY. Otherwise PostgreSQL stops you.

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

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON (customer_id) customer_id, order_id, orde...
                            ^

Step 2: break ties

If a customer has two orders on the same day, "the latest" is not defined, and PostgreSQL may keep either one. Add a column that makes the order unique.

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

 customer_id | order_id | order_date
-------------+----------+------------
           1 |       11 | 2026-09-20
           2 |       10 | 2026-09-14
           3 |        6 | 2026-09-02
           5 |        8 | 2026-09-10
           6 |        9 | 2026-09-12
           8 |       12 | 2026-09-25
(6 rows)

If the column can be NULL, remember that NULL sorts first in a descending order. Write order_date DESC NULLS LAST so a missing date cannot win.

Step 3: bring in the customer's name

Join first, then use DISTINCT ON, or join the result. Here the join comes first.

SELECT DISTINCT ON (c.customer_id) c.name, o.order_id, o.order_date
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_date DESC, o.order_id DESC;

    name    | order_id | order_date
------------+----------+------------
 Ana Reyes  |       11 | 2026-09-20
 Ben Cruz   |       10 | 2026-09-14
 Carla Diaz |        6 | 2026-09-02
 Ella Tan   |        8 | 2026-09-10
 Finn Go    |        9 | 2026-09-12
 Hugo Sy    |       12 | 2026-09-25
(6 rows)

Customers with no orders are not here, because it is an INNER JOIN. Use a LEFT JOIN if you want them, with NULL in the order columns.

The standard version

DISTINCT ON is PostgreSQL-only. The portable way is row_number() and a filter, which also works in PostgreSQL and gives the same answer.

SELECT customer_id, order_id, order_date
FROM (
  SELECT customer_id, order_id, order_date,
         row_number() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders
) AS ranked
WHERE rn = 1
ORDER BY customer_id;

 customer_id | order_id | order_date
-------------+----------+------------
           1 |       11 | 2026-09-20
           2 |       10 | 2026-09-14
           3 |        6 | 2026-09-02
           5 |        8 | 2026-09-10
           6 |        9 | 2026-09-12
           8 |       12 | 2026-09-25
(6 rows)

Use DISTINCT ON when you only need PostgreSQL and want the short form. Use row_number() when the query may move to another database, or when you need more than one row per group. That is the Top 3 Rows per Group tutorial.

Related course: SQL Intermediate.