PostgreSQL DISTINCT ON: One Row per Group, With Examples

SELECT DISTINCT ON (column) returns exactly one row for each distinct value of the column you list, and the ORDER BY clause decides which row that is. It is the shortest way in PostgreSQL to answer questions like "the latest order for each customer" or "the most expensive product in each category" while still getting every column of that row.

The rule that trips most people up: the DISTINCT ON expressions must be the first expressions in ORDER BY, and the columns after them pick the winner. This tutorial shows the syntax on a small orders table, what happens with ties and NULLs, how to get more than one row per group, and how DISTINCT ON compares with row_number() and a LATERAL join when the table is large. Every query was run and the output is shown.

How DISTINCT ON works

Plain SELECT DISTINCT removes rows that are identical in every selected column. DISTINCT ON (expression, ...) is looser: two rows count as duplicates when only the listed expressions are equal, even if the other columns differ. From each set of duplicates PostgreSQL keeps the first row and throws the rest away.

"First" means first in the sort order. PostgreSQL sorts the rows (or reads them in order from an index), then walks through them and keeps a row only when the DISTINCT ON value changes. That is why the documentation warns that the first row of each set is unpredictable unless ORDER BY puts the row you want at the top of its group.

So a DISTINCT ON query has two parts that work together:

• DISTINCT ON (customer_id): defines the groups, one output row per customer.
• ORDER BY customer_id, order_date DESC: the leading customer_id must match the DISTINCT ON list; order_date DESC ranks rows inside each group so the newest order comes first and wins.

DISTINCT ON is a PostgreSQL extension; it is not in the SQL standard. Oracle, SQL Server and MySQL do not have it, so if you are coming from Oracle, think of it as a short form of the ROW_NUMBER() OVER (PARTITION BY ...) pattern you already know.

Sample setup

Create a small orders table. It has two customers with ties or NULLs on purpose, because those are the cases where DISTINCT ON surprises people: customer 103 placed two orders on the same day, and customer 104 has one order with no date.

CREATE TABLE orders (
    order_id     integer PRIMARY KEY,
    customer_id  integer NOT NULL,
    order_date   date,
    amount       numeric(10,2) NOT NULL,
    status       text NOT NULL
);

INSERT INTO orders (order_id, customer_id, order_date, amount, status) VALUES
    (1, 101, '2026-09-02', 1200.00, 'delivered'),
    (2, 101, '2026-09-20',  450.00, 'delivered'),
    (3, 101, '2026-10-05',  980.00, 'shipped'),
    (4, 102, '2026-08-14', 3100.00, 'delivered'),
    (5, 102, '2026-10-01',  750.00, 'cancelled'),
    (6, 103, '2026-09-11',  300.00, 'delivered'),
    (7, 103, '2026-09-11',  820.00, 'delivered'),
    (8, 104, NULL,          640.00, 'pending'),
    (9, 104, '2026-07-30',  210.00, 'delivered');

DISTINCT vs DISTINCT ON

Start with plain DISTINCT to see the difference. It can only return the columns that define uniqueness:

SELECT DISTINCT customer_id
  FROM orders
 ORDER BY customer_id;

As soon as you add order_id or amount to a plain DISTINCT, every row becomes unique again and you get all nine back. A GROUP BY has the same limit: you can get the latest date per customer, but not the other columns of that row.

SELECT customer_id, max(order_date) AS last_order_date, order_id
  FROM orders
 GROUP BY customer_id;

ERROR:  column "orders.order_id" must appear in the GROUP BY clause or be used in an aggregate function

DISTINCT ON fixes exactly this problem: one row per group, with all of its columns.

Example: latest order for each customer

Step 1: the basic query

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

Customers 101 and 102 look right. Two rows are wrong, and they are the two most common DISTINCT ON bugs:

• Customer 104 got order 8, the one with no date. In descending order NULLs come first.
• Customer 103 got order 6, but order 7 has the same date. Which of the two you get is not guaranteed.

Step 2: put NULL dates last

The documentation states that NULLs sort as if larger than any non-null value, so NULLS FIRST is the default for DESC and NULLS LAST for ASC. When you sort newest first, add NULLS LAST:

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

Customer 104 now gets order 9, the newest order that has a date.

Step 3: add a tie-breaker

Orders 6 and 7 are equal on customer_id and order_date, so the sort can return either first. The result can change after an UPDATE, a VACUUM, a new index or a different plan. Add a unique column at the end of ORDER BY so the order is fully defined:

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

Now the answer is the same every time: for a same-day tie, the higher order_id (the order created later) wins.

Choosing the winner by a different column

The DISTINCT ON column stays the same; only the columns after it change. To get each customer's largest order, sort by amount DESC:

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

A WHERE clause is applied before DISTINCT ON, so it filters the candidates. Here cancelled orders are excluded, and customer 102 falls back to order 4:

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, order_date, status
  FROM orders
 WHERE status <> 'cancelled'
 ORDER BY customer_id, order_date DESC NULLS LAST, order_id DESC;

DISTINCT ON multiple columns and expressions

List several expressions to get one row per combination. This returns the latest order for every customer and status pair:

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

The expressions do not have to be plain columns. Any expression works, as long as the same expression leads the ORDER BY. This finds the biggest order in each calendar month:

SELECT DISTINCT ON (date_trunc('month', order_date))
       date_trunc('month', order_date)::date AS month,
       order_id, customer_id, amount
  FROM orders
 WHERE order_date IS NOT NULL
 ORDER BY date_trunc('month', order_date), amount DESC;

Note on NULLs: like plain DISTINCT, DISTINCT ON treats NULL values as equal to each other, so all rows with a NULL key form one group and return one row. That is why the query above filters out the undated order first.

Sorting the final result differently

Because ORDER BY must start with the DISTINCT ON columns, the output always comes back sorted by customer. If you want the latest orders listed by amount, wrap the query in a subquery and sort outside it:

SELECT *
  FROM (
        SELECT DISTINCT ON (customer_id)
               customer_id, order_id, order_date, amount
          FROM orders
         ORDER BY customer_id, order_date DESC NULLS LAST, order_id DESC
       ) AS latest
 ORDER BY amount DESC;

Trying to do this in one level gives an error, shown in the common mistakes section below.

DISTINCT ON vs row_number()

The standard SQL way to get the same result is the window function row_number(), which numbers rows within each partition starting from 1. Keep the rows numbered 1:

SELECT customer_id, order_id, order_date, amount
  FROM (
        SELECT o.*,
               row_number() OVER (PARTITION BY customer_id
                                  ORDER BY order_date DESC NULLS LAST, order_id DESC) AS rn
          FROM orders o
       ) AS ranked
 WHERE rn = 1
 ORDER BY customer_id;

Same four rows. The advantage of row_number() is flexibility: change rn = 1 to rn <= 2 and you get the two latest orders per customer, something DISTINCT ON cannot do.

SELECT customer_id, order_id, order_date, amount
  FROM (
        SELECT o.*,
               row_number() OVER (PARTITION BY customer_id
                                  ORDER BY order_date DESC NULLS LAST, order_id DESC) AS rn
          FROM orders o
       ) AS ranked
 WHERE rn <= 2
 ORDER BY customer_id, rn;

If you need all tied rows (both of customer 103's orders on 2026-09-11, say), use rank() instead of row_number(): the documentation defines it as the row_number of the first row in its peer group, so tied rows get the same rank.

Performance: making DISTINCT ON fast

To test speed, the same queries were run on a table with 500,000 orders spread over 2,000 customers. Without a suitable index, PostgreSQL has to read the whole table and sort it before the Unique step can drop the duplicates:

EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date, amount
  FROM orders_big
 ORDER BY customer_id, order_date DESC, order_id DESC;

 Unique
   ->  Sort
         Sort Key: customer_id, order_date DESC, order_id DESC
         ->  Seq Scan on orders_big

Step 1: an index that matches the ORDER BY

A B-tree index can return rows already sorted, which removes the Sort step. The index columns and directions must match the ORDER BY exactly:

CREATE INDEX orders_big_cust_latest_idx
    ON orders_big (customer_id, order_date DESC, order_id DESC);

 Result
   ->  Unique
         ->  Index Scan using orders_big_cust_latest_idx on orders_big

The Sort is gone, but the index scan still reads all 500,000 index entries and keeps 2,000 of them. On the test machine this query and the row_number() version both took around 140 to 170 ms. If your query uses NULLS LAST, add it to the index column too (order_date DESC NULLS LAST); otherwise the index order no longer matches, and the plan gets an extra sort step again (in our test an Incremental Sort on top of the index scan).

Step 2: many rows per group? Use a LATERAL join

When each group has hundreds of rows, it is cheaper to jump straight to the top row of each group than to read every row. If you have a table with one row per customer, a LATERAL subquery with LIMIT 1 does exactly that: one short index lookup per customer.

SELECT c.customer_id, latest.order_id, latest.order_date, latest.amount
  FROM customers c
 CROSS JOIN LATERAL (
        SELECT b.order_id, b.order_date, b.amount
          FROM orders_big b
         WHERE b.customer_id = c.customer_id
         ORDER BY b.order_date DESC, b.order_id DESC
         LIMIT 1
       ) AS latest;

 Nested Loop
   ->  Seq Scan on customers c
   ->  Limit
         ->  Index Scan using orders_big_cust_latest_idx on orders_big b
               Index Cond: (customer_id = c.customer_id)

On the same data this returned the same 2,000 rows in about 8 to 10 ms on the test machine, because it reads 2,000 index entries instead of 500,000. Your timings will differ, but the pattern holds: the fewer groups compared with rows, the bigger the gain. With a CROSS JOIN, customers without orders are left out; use LEFT JOIN LATERAL (...) ON true to keep them with NULLs.

Common mistakes

ORDER BY does not start with the DISTINCT ON columns

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date, amount
  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...

Fix: make the DISTINCT ON columns the leading ORDER BY columns (ORDER BY customer_id, order_date DESC). If you want the result ordered by date, wrap the query in a subquery and sort outside, as shown earlier.

Leaving out ORDER BY

Without ORDER BY the query runs without error but returns whichever row PostgreSQL meets first in each group:

SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date, amount
  FROM orders;

This happened to return the oldest orders here, but nothing guarantees it. Always write the ORDER BY that names the winner.

Forgetting NULLS LAST and the tie-breaker

Shown in the steps above: DESC puts NULLs first, and a non-unique last sort column makes the result unpredictable. End the ORDER BY with the primary key.

Using FOR UPDATE with DISTINCT ON

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

ERROR:  FOR UPDATE is not allowed with DISTINCT clause

The documentation lists this restriction: row locking clauses cannot be used with DISTINCT. Select the order ids first, then lock them with SELECT ... FROM orders WHERE order_id IN (...) FOR UPDATE. For queue-style locking see PostgreSQL FOR UPDATE SKIP LOCKED.

Frequently asked questions

Is DISTINCT ON faster than GROUP BY?

They answer different questions. GROUP BY with max() gives you aggregates only; DISTINCT ON gives you the whole row. When you need the full row, DISTINCT ON is usually simpler and at least as fast as joining a GROUP BY result back to the table. Check your own case with EXPLAIN ANALYZE.

Can I use DISTINCT ON with multiple columns?

Yes. DISTINCT ON (customer_id, status) returns one row per combination. The ORDER BY must start with the same columns, in the same order.

How do I count rows with DISTINCT ON?

DISTINCT ON does not aggregate. To count groups, use count(DISTINCT customer_id) (it returns 4 on the sample table), or wrap the DISTINCT ON query in a subquery and run count(*) over it.

Does DISTINCT ON work in Oracle or MySQL?

No. It is a PostgreSQL extension to the SQL standard. In Oracle, SQL Server and MySQL 8, use row_number() OVER (PARTITION BY ...) as shown above; see also SQL window functions with examples.

Summary

• DISTINCT ON (cols) keeps one row per distinct value of cols, with all its columns.
• ORDER BY must start with the DISTINCT ON expressions; the columns after them choose the winning row.
• Add NULLS LAST when sorting DESC on a nullable column.
• End ORDER BY with a unique column (usually the primary key) so the result is deterministic.
• Wrap the query in a subquery to sort the final result by something else.
• Use row_number() for top N per group or portable SQL, rank() to keep ties.
• Create an index that matches the ORDER BY, and try a LATERAL ... LIMIT 1 join when groups are large.