How to select the first row in each group in SQL

1ROW_NUMBER() in a CTE, then WHERE rn = 1Recommended

ROW_NUMBER() OVER (PARTITION BY customer ORDER BY placed DESC) restarts the count at 1 for every customer and hands out 1 to the newest order. You can't filter on a window function in the same query's WHERE (it is computed after WHERE runs), so put the numbering in a CTE or a derived table and filter outside it. Flip the sort to ASC for the first order instead of the last.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    placed   TEXT,
    total    INTEGER
);
INSERT INTO orders VALUES
    (1, 'ana', '2026-03-02', 40),
    (2, 'ben', '2026-03-03', 15),
    (3, 'ana', '2026-03-09', 25),
    (4, 'cy',  '2026-03-10', 60),
    (5, 'ben', '2026-03-12', 35),
    (6, 'ana', '2026-03-14', 10);

-- Number each customer's orders, newest first, then keep number 1
WITH ranked AS (
    SELECT id, customer, placed, total,
           ROW_NUMBER() OVER (
               PARTITION BY customer
               ORDER BY placed DESC, id DESC
           ) AS rn
    FROM orders
)
SELECT id, customer, placed, total
FROM ranked
WHERE rn = 1
ORDER BY customer;

One row per customer, each the full order: ana's order 6 from 2026-03-14, ben's order 5 and cy's only order, 4. The id DESC in the window's ORDER BY is a tie-breaker. ROW_NUMBER() always returns exactly one row numbered 1 per group, but if two orders share a date, which one gets the 1 is up to the database unless the sort settles it. With a unique column last in the ORDER BY, the answer is the same on every run. Add more columns to PARTITION BY to group by several columns at once.

2Top N per group: rn <= N

The same numbering answers "the two most recent orders per customer": filter on rn <= 2 instead of rn = 1. Selecting rn as well shows how the count restarts for each customer.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    placed   TEXT,
    total    INTEGER
);
INSERT INTO orders VALUES
    (1, 'ana', '2026-03-02', 40),
    (2, 'ben', '2026-03-03', 15),
    (3, 'ana', '2026-03-09', 25),
    (4, 'cy',  '2026-03-10', 60),
    (5, 'ben', '2026-03-12', 35),
    (6, 'ana', '2026-03-14', 10);

-- The two most recent orders per customer
WITH ranked AS (
    SELECT customer, placed, total,
           ROW_NUMBER() OVER (
               PARTITION BY customer
               ORDER BY placed DESC, id DESC
           ) AS rn
    FROM orders
)
SELECT customer, rn, placed, total
FROM ranked
WHERE rn <= 2
ORDER BY customer, rn;

ana has three orders and returns her two newest, numbered 1 and 2; her order from 2026-03-02 is rank 3 and drops out. cy has only one order, so cy returns one row: a group smaller than N simply returns all of its rows. A plain LIMIT 2 on the whole query couldn't do this, because it counts rows across every customer, not within each one.

3Join to a MAX() subquery, and the tie problem

Before window functions, the usual answer was two steps: a GROUP BY subquery finds each customer's latest date, and a join back to the table fetches the orders on that date. It runs anywhere, but it matches on a value, not on a row, so every order that shares the latest date comes back. This example adds a second order for ben on his last day.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    placed   TEXT,
    total    INTEGER
);
INSERT INTO orders VALUES
    (1, 'ana', '2026-03-02', 40),
    (2, 'ben', '2026-03-03', 15),
    (3, 'ana', '2026-03-09', 25),
    (4, 'cy',  '2026-03-10', 60),
    (5, 'ben', '2026-03-12', 35),
    (6, 'ana', '2026-03-14', 10),
    (7, 'ben', '2026-03-12', 20);  -- ben orders twice on his last day

-- Each customer's latest date, joined back to the orders on that date
SELECT o.id, o.customer, o.placed, o.total
FROM orders AS o
JOIN (
    SELECT customer, MAX(placed) AS last_placed
    FROM orders
    GROUP BY customer
) AS m
  ON  m.customer = o.customer
  AND m.last_placed = o.placed
ORDER BY o.customer, o.id;

Four rows for three customers: ben's orders 5 and 7 both match his latest date. Sometimes that is what you want, but code that expects one row per customer breaks quietly. With the same seven rows, the ROW_NUMBER() query from section 1 returns a single row for ben, order 7, because its id DESC tie-breaker ranks it first. If you keep the join, make the latest value unique (a full timestamp, or a second MAX(id) step), or switch to section 5's RANK() so the ties are a deliberate choice.

4A correlated subquery, or NOT EXISTS

A correlated subquery asks the same question row by row: keep this order if its date equals the latest date for its own customer. The inner query refers to the outer row through the alias o. The NOT EXISTS form turns it around and keeps an order when no later order exists for the same customer.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    placed   TEXT,
    total    INTEGER
);
INSERT INTO orders VALUES
    (1, 'ana', '2026-03-02', 40),
    (2, 'ben', '2026-03-03', 15),
    (3, 'ana', '2026-03-09', 25),
    (4, 'cy',  '2026-03-10', 60),
    (5, 'ben', '2026-03-12', 35),
    (6, 'ana', '2026-03-14', 10);

-- Keep an order when it has the latest date for its customer
SELECT id, customer, placed, total
FROM orders AS o
WHERE o.placed = (
    SELECT MAX(i.placed)
    FROM orders AS i
    WHERE i.customer = o.customer
)
ORDER BY customer;

-- The same question: no later order exists for this customer
SELECT id, customer, placed
FROM orders AS o
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS later
    WHERE later.customer = o.customer
      AND later.placed > o.placed
)
ORDER BY customer;

Both queries return orders 6, 5 and 4, the same rows as section 1. Both also share the join's tie problem: add section 3's order 7 and each one returns ben twice, orders 5 and 7. They are short to write and work in every database, and with an index on (customer, placed) the inner lookup is cheap. Without that index the database may rerun the subquery for every row, so on a large table check the plan or prefer ROW_NUMBER().

5RANK() to keep ties on purpose

When a tie should return every tied row, say both laps if a driver set the same best time twice, use RANK() in place of ROW_NUMBER(). Rows with equal sort values get the same rank, so WHERE rnk = 1 keeps all of them. This example selects both numbers side by side.

CREATE TABLE laps (
    driver TEXT,
    lap    INTEGER,
    secs   REAL
);
INSERT INTO laps VALUES
    ('kai', 1, 82.9), ('kai', 2, 81.4), ('kai', 3, 81.4),
    ('mo',  1, 83.0), ('mo',  2, 82.1), ('mo',  3, 82.6),
    ('zoe', 1, 80.7), ('zoe', 2, 80.9);

-- Fastest lap per driver: RANK() gives tied laps the same number,
-- ROW_NUMBER() still counts 1, 2
WITH ranked AS (
    SELECT driver, lap, secs,
           RANK() OVER (
               PARTITION BY driver ORDER BY secs
           ) AS rnk,
           ROW_NUMBER() OVER (
               PARTITION BY driver ORDER BY secs, lap
           ) AS rn
    FROM laps
)
SELECT driver, lap, secs, rnk, rn
FROM ranked
WHERE rnk = 1
ORDER BY driver, lap;

kai's laps 2 and 3 both ran 81.4, so both have rnk 1 and both come back, while their rn values are 1 and 2: filtering on rn = 1 would have kept only lap 2. mo and zoe have a single fastest lap each. RANK() skips numbers after a tie (the next kai lap would be rank 3); DENSE_RANK() doesn't, which matters when you filter on rnk <= N and want N distinct times rather than N positions.

6SQLite only: bare columns next to MAX()

SQLite lets you select columns that are neither grouped nor aggregated. When the query has a single MAX() or MIN(), SQLite fills those bare columns from the row that holds the maximum or minimum, so GROUP BY customer with MAX(placed) returns each customer's latest order in one short query. It is documented SQLite behaviour, and it doesn't carry over to other databases.

CREATE TABLE orders (
    id       INTEGER PRIMARY KEY,
    customer TEXT,
    placed   TEXT,
    total    INTEGER
);
INSERT INTO orders VALUES
    (1, 'ana', '2026-03-02', 40),
    (2, 'ben', '2026-03-03', 15),
    (3, 'ana', '2026-03-09', 25),
    (4, 'cy',  '2026-03-10', 60),
    (5, 'ben', '2026-03-12', 35),
    (6, 'ana', '2026-03-14', 10);

-- SQLite only: id and total are not grouped or aggregated, so SQLite
-- takes them from the row that holds MAX(placed)
SELECT customer, id, MAX(placed) AS placed, total
FROM orders
GROUP BY customer;

SQLite returns orders 6, 5 and 4 with their own totals, the same rows as section 1. PostgreSQL refuses the query with ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function, and SQL Server rejects it too. MySQL 8.4 refuses it under its default ONLY_FULL_GROUP_BY mode with ERROR 1055 (42000); with that mode switched off it runs, but it doesn't look for the max row: on the same data it returned ana with id 1 and total 40, values from her first order beside her latest date. Use this shortcut only in SQLite-only code, and with one MAX() or MIN() in the query.

7Other databases: PostgreSQL, MySQL and SQL Server

The ROW_NUMBER() query from section 1 runs unchanged on PostgreSQL, MySQL 8 and SQL Server (Oracle has ROW_NUMBER() as well). Each database also has its own shortcut, and the per-group subquery forms (LATERAL, CROSS APPLY) are handy for top N per group when an index on (customer, placed) exists.