Example 1
Keep the latest order per customer
Each customer's rows are ranked newest-first, then only row_num = 1 is kept.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE orders (id INT, customer VARCHAR(50), created_at DATE);
INSERT INTO
orders (id, customer, created_at)
VALUES
(1, 'Ada', '2024-01-01'),
(2, 'Ada', '2024-02-01'),
(3, 'Linus', '2024-01-15'),
(4, 'Linus', '2024-03-01');Validated query Shared across supported engines.
SQL
WITH
ranked AS (
SELECT
id,
customer,
created_at,
ROW_NUMBER() OVER (
PARTITION BY
customer
ORDER BY
created_at DESC,
id DESC
) AS row_num
FROM
orders
)
SELECT
customer,
id,
created_at
FROM
ranked
WHERE
row_num = 1
ORDER BY
customer;Expected result Returned rows for the shared example.
| customer | id | created_at |
|---|---|---|
| Ada | 2 | 2024-02-01 |
| Linus | 4 | 2024-03-01 |
The same ranking pattern and final result validate across all supported engines here.