Latest Order per Status — DISTINCT ON in SQL

The problem

Brightlane's operations director is reviewing the most recent order in each status group for a pipeline health check.

Write a query to return one row per status, showing the status, the ID of the most recent order in that status, when it was placed, and the order amount. Sort the final result by status ascending.

Assumptions:

The most recent order in a status is the order with the largest ordered_at for that status value.
Each unique status value present in the data should appear once.
The final result is sorted by status ascending.

Output:

One row per status, with columns status, order_id, ordered_at, and total_amount. Sorted by status.

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 (status) status,
  id AS order_id,
  ordered_at,
  total_amount
FROM
  orders
ORDER BY
  status,
  ordered_at DESC

The shape

DISTINCT ON (status) keeps one row per distinct status value, and ORDER BY status, ordered_at DESC picks the most recent order in each status. The result is the pipeline-health snapshot: one freshest order per status group, with the order data attached.

Clause by clause

SELECT DISTINCT ON (status) status, id AS order_id, ordered_at, total_amount returns the four columns the operations review needs. The DISTINCT ON (status) part declares the deduplication key: one row per distinct status value. The id AS order_id alias names the order identifier column.
FROM orders reads the order records.
ORDER BY status, ordered_at DESC sorts each status group's rows so the most recent order sits first. PostgreSQL walks the sorted rows and takes the first row for each new status value. The leading status ascending also gives the final result the alphabetised status order the prompt asks for.

Why this and not `ROW_NUMBER`

The same picks can be made with a window function and a subquery:

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

Both return the same four rows. DISTINCT ON says the intent in one clause: deduplicate on status, keep the most recent. The ROW_NUMBER form is more portable but takes a subquery, a window function, and a filter to express the same thing.

You practiced DISTINCT ON (status) with ORDER BY status, ordered_at DESC — keep one record per category, picked by the secondary sort.

Return one row per status, showing the status, the ID of the most recent order in that status, when it was placed, and the order amount. Sort the final result by `status` 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`