Latest Order per Customer — DISTINCT ON in SQL

The problem

Brightlane's CRM team is building a customer overview that shows each customer's most recent purchase.

Write a query to return one row per customer with at least one order, showing that customer's ID, the ID of their most recent order, when it was placed, and the order amount. Sort the final result by customer_id ascending.

Assumptions:

A customer's most recent order is the order with the largest ordered_at for that customer_id.
Customers with no orders on record do not appear in the result.
The final result is sorted by customer_id ascending.

Output:

One row per customer with at least one order, with columns customer_id, order_id, ordered_at, and total_amount. Sorted by customer_id.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT DISTINCT
  ON (customer_id) customer_id,
  id AS order_id,
  ordered_at,
  total_amount
FROM
  orders
ORDER BY
  customer_id,
  ordered_at DESC

The shape

DISTINCT ON (customer_id) keeps exactly one row per customer, and the ORDER BY customer_id, ordered_at DESC decides which row that is — the one with the largest ordered_at. One clause does the entire latest-per-customer pick, with the full order data attached.

Clause by clause

SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, ordered_at, total_amount returns the four columns the CRM overview needs. The DISTINCT ON (customer_id) part declares that the result will contain one row per distinct customer_id. The id AS order_id alias makes the output column read as an order identifier instead of a generic id.
FROM orders reads the order records. Customers with no orders never enter this row source, so they cannot appear in the result, which matches the prompt.
ORDER BY customer_id, ordered_at DESC sorts the orders so that within each customer's group, the most recent order sits first. PostgreSQL walks the sorted rows and keeps the first row it sees for each new customer_id value. The customer_id ascending in the leading position also gives the final result the customer-ordered shape the prompt asks for.

Why this and not `ROW_NUMBER`

The same result is reachable with a window function and a subquery:

SELECT customer_id, order_id, ordered_at, total_amount
FROM (
  SELECT customer_id, id AS order_id, ordered_at, total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1
ORDER BY customer_id

Both return the same rows. DISTINCT ON is shorter, names the intent in a single clause, and is the PostgreSQL-idiomatic choice for the latest-per-group shape. The ROW_NUMBER version is more portable to other databases but takes more reading to understand the same intent.

You practiced DISTINCT ON (key) ... ORDER BY key, sort_col DESC — PostgreSQL keeps the first record per key after sorting; the canonical 'latest per group' shape.

Return one row per customer with at least one order, showing that customer's ID, the ID of their most recent order, when it was placed, and the order amount. Sort the final result by `customer_id` ascending

The shape

Clause by clause

Why this and not `ROW_NUMBER`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not ROW_NUMBER

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `ROW_NUMBER`