The latest order per customer in PostgreSQL with DISTINCT ON

You want one row per group from a table: the newest row of each group, with all of its columns. PostgreSQL has DISTINCT ON for this. You name the column that defines the group, sort so the row you want comes first, and PostgreSQL keeps the first row of each group.

The examples run on PostgreSQL 17.6 against these tables:

CREATE TABLE customers (customer_id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
  order_id int PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers,
  placed_at timestamptz NOT NULL,
  total numeric(10,2) NOT NULL
);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO orders VALUES
  (10, 1, '2026-03-01 09:00+00', 40.00),
  (11, 1, '2026-03-05 14:30+00', 12.50),
  (12, 2, '2026-03-02 11:15+00', 99.90);

Each customer has several orders, and the report needs the most recent one per customer, with its date and its total. GROUP BY customer_id with max(placed_at) gives you the date of the newest order, but nothing else from that row. The order's id and its total aren't aggregates, so GROUP BY can't return them. DISTINCT ON keeps the whole row.

The query and the ORDER BY it requires

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

PostgreSQL sorts the rows, then keeps the first row it sees for each distinct customer_id. That only works if the sort groups the rows by customer first, so the ORDER BY has to start with the DISTINCT ON expressions. Leave customer_id out of it and the query is rejected:

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

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

What happens when two orders have the same timestamp

A second order for customer 1 arrives with the same timestamp as order 11:

INSERT INTO orders VALUES (13, 1, '2026-03-05 14:30+00', 7.00);

Run the first query again and you get order 11 or order 13 for customer 1, and nothing in the query says which. The PostgreSQL documentation says the first row among equals is unpredictable unless ORDER BY decides it, so the answer can change with the plan. Add a column that breaks the tie:

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

Order 13 wins the tie:

Every DISTINCT ON query needs an ORDER BY that identifies exactly one row per group, and the primary key at the end of the list is the cheapest way to get there.

Sorting the result by something else

The ORDER BY belongs to DISTINCT ON, so the result comes back sorted by customer. To sort the latest orders by date instead, wrap the query:

SELECT *
FROM (
  SELECT DISTINCT ON (customer_id) customer_id, order_id, placed_at, total
  FROM orders
  ORDER BY customer_id, placed_at DESC, order_id DESC
) latest
ORDER BY placed_at DESC;

When you need more than one row per group

DISTINCT ON keeps one row per group and nothing else. For the last two orders per customer, number the rows inside each group and filter:

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

For a single row per group, write DISTINCT ON: it is shorter, and the sort that picks the row is in one place. Write ROW_NUMBER when you need more than one row per group, or when the query has to run unchanged on another engine, because DISTINCT ON is a PostgreSQL extension and window functions are standard SQL.

Customers with no orders at all

Both queries above start from orders, so a customer who never ordered is absent from the result. To list every customer with their latest order, or nothing, start from customers and fetch the one order per row with a lateral join:

SELECT c.customer_id, c.name, o.order_id, o.placed_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_id, placed_at
  FROM orders
  WHERE customer_id = c.customer_id
  ORDER BY placed_at DESC, order_id DESC
  LIMIT 1
) o ON true
ORDER BY c.customer_id;

Customer 3 appears with empty order columns:

The subquery runs once per customer and sees that customer's id, which is what LATERAL adds over a plain join, and LEFT keeps the customer whose subquery returns nothing.

The queries run as written in the SQL editor of the free DbSchema Community Edition. Download it at https://dbschema.com/download.html, connect to your PostgreSQL database, and paste them in.