FORMULA & SQL / LOOKUPS

Keep the latest row for each group.
Tested, not guessed.

The Excel and Google Sheets formula and the SQL query, each run on the rows below. Open this with your own data ↗

The example rows

Row	A: Region	B: Rep	C: Amount	D: Order Date	E: Status	F: Customer
2	North	Ana	1,200	2026-03-01	Paid	Jo Smith
3	South	Ben	450	2026-03-15	Open	Raj Patel
4	North	Cy	300	2026-04-02	Paid	Lee Wong
5	East	Ana	980.50	2026-04-20	Paid	Mia Ross
6	North	Ben	75	2026-05-05	Open	Jo Smith
7	West	Dee	1,640	2026-05-18	Paid	Sam Okoro

The formula

Excel

=FILTER(A2:F7,D2:D7=MAXIFS(D2:D7,A2:A7,A2:A7))

Google Sheets

=QUERY(A1:F7,"select A, max(D) group by A",1)

The rows of the range A2:F7 where the Order Date column (D2:D7) equals the largest value of the Order Date column (D2:D7) where the Region column (A2:A7) is the Region column (A2:A7).

Run on the example rows:

A	B	C	D	E	F
South	Ben	450	2026-03-15	Open	Raj Patel
East	Ana	980.5	2026-04-20	Paid	Mia Ross
North	Ben	75	2026-05-05	Open	Jo Smith
West	Dee	1640	2026-05-18	Paid	Sam Okoro

The SQL

PostgreSQL

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1

Run on the example rows:

region	rep	amount	order_date	status	customer	rn
East	Ana	980.5	2026-04-20	Paid	Mia Ross	1
North	Ben	75	2026-05-05	Open	Jo Smith	1
South	Ben	450	2026-03-15	Open	Raj Patel	1
West	Dee	1640	2026-05-18	Paid	Sam Okoro	1

Other databases

MySQL

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1

SQL Server

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1

SQLite

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1

BigQuery

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1

Snowflake

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY order_date DESC) AS rn
  FROM sales
) t
WHERE rn = 1

Your own data

Paste your rows into the free tool and it writes this for your columns, then runs it on your data before you copy it. Open the tool ↗

Keep the latest row for each group. Tested, not guessed.

The example rows

The formula

The SQL

Your own data

Related

Keep the latest row for each group.
Tested, not guessed.