Remove Duplicates Keeping the Latest Row Both Ways

A table of customer records has three rows for the same customer: one from 2024, one from 2025 and one from this March. The business wants one row per customer, the newest. "Remove duplicates" is one click in Excel and a few lines in SQL, and in both the tool needs to be told which row to keep. Left to defaults, Excel keeps the one that happens to be first in the sheet, and a SQL query without an order returns any of them. This article shows how to keep the latest, deliberately, in both. It is one of the worked pairs in moving between Excel and SQL, and the basic DISTINCT version is in removing duplicates in Excel and SQL.

Sample data

A table customers(customer_id, name, email, updated_on) with repeated IDs:

Customer 10 has three rows. The row to keep is the one dated 2026-03-04, with the name "Acme Ltd". The result should have three rows: customers 10, 11 and 12.

Excel: sort, then Remove Duplicates

The command Data, Remove Duplicates deletes rows that repeat the chosen columns, keeping the first occurrence from the top of the range. The rule is positional, so the order of the sheet decides which row survives.

1. Sort the range by updated_on descending (Data, Sort, Largest to Smallest), with the key as the first sort level if you like.

2. Select the range, choose Data, Remove Duplicates and tick only customer_id.

3. Check the message: "2 duplicate values found and removed; 3 unique values remain."

After the sort, customer 10's newest row is on top, so it is the one kept. The weakness is that the command is destructive and unrecorded: nothing in the file says which rows were removed or why, and doing it on the original sheet loses the history. Work on a copy, or on a new sheet that you build from the original.

If two rows for a key share the latest date, the sort order between them is whatever Excel leaves, so add a second sort level (a version number, an ID) to make the choice deterministic.

Excel: formulas that leave the source alone

A formula approach produces the deduplicated list on another sheet and updates when the source changes.

Latest per key with SORTBY and UNIQUE (Microsoft 365):

=LET(
  d, SORTBY(Customers, Customers[updated_on], -1),
  UNIQUE(d, FALSE, FALSE) )

That sorts descending by date, then UNIQUE on whole rows would keep rows that differ anywhere, which is not what we want. Select the key column and filter instead:

=LET(
  d, SORTBY(Customers, Customers[updated_on], -1),
  k, INDEX(d, , 1),
  FILTER(d, MATCH(k, k, 0) = SEQUENCE(ROWS(k))))

MATCH(k, k, 0) returns, for each row, the position of the first occurrence of its key. A row is the first occurrence when that position equals its own row number. Because the data is sorted newest first, the first occurrence of each key is its latest row. The formula returns the kept rows in descending date order; wrap in SORTBY again if you want them by ID.

Latest value for one key with XLOOKUP:

=XLOOKUP(10, Customers[customer_id], Customers[updated_on], , 0, -1)

search_mode -1 searches from the last row upward, so it finds the last matching row in sheet order. That is the latest only if the sheet is in date order ascending. Make it explicit by looking up on the maximum date instead: =MAXIFS(Customers[updated_on], Customers[customer_id], 10) gives the latest date for the key, and a second lookup on (key, date) returns the row, as in joining two sheets on two keys in Excel and in SQL.

SQL: DISTINCT ON in PostgreSQL

SELECT DISTINCT ON (customer_id) customer_id, name, email, updated_on
FROM customers
ORDER BY customer_id, updated_on DESC, name;

DISTINCT ON (customer_id) keeps the first row for each key according to the ORDER BY, and the order must begin with the same expression. The remaining ORDER BY terms pick the winner: newest updated_on first, with name as the tiebreaker. The result is customers 10 (the 2026-03-04 row, "Acme Ltd"), 11 and 12. DISTINCT ON is PostgreSQL-specific, short and fast.

SQL: ROW_NUMBER in any engine

SELECT customer_id, name, email, updated_on
FROM (
  SELECT c.*,
         row_number() OVER (PARTITION BY customer_id ORDER BY updated_on DESC, name) AS rn
  FROM customers c
) t
WHERE rn = 1;

The window numbers each key's rows from newest to oldest, and the outer query keeps number 1. It works in PostgreSQL, MySQL 8, SQL Server, SQLite and DuckDB. DuckDB and a few other engines add QUALIFY row_number() OVER (...) = 1, which removes the subquery. A third form joins to the per-key maximum:

SELECT c.*
FROM customers c
JOIN (SELECT customer_id, max(updated_on) AS m FROM customers GROUP BY customer_id) x
  ON x.customer_id = c.customer_id AND x.m = c.updated_on;

This one returns every row tied for the latest date, so two rows with the same maximum both come through. That is a feature when ties should be visible, and a bug when you wanted exactly one row. Know which you chose.

Deleting duplicates in the table itself

Sometimes the cleanup must change the table. In PostgreSQL, delete everything except the latest row per key:

DELETE FROM customers c
USING (
  SELECT ctid, row_number() OVER (PARTITION BY customer_id ORDER BY updated_on DESC, ctid) AS rn
  FROM customers
) d
WHERE c.ctid = d.ctid AND d.rn > 1;

ctid is PostgreSQL's physical row address, used when the table has no unique ID. Always run the matching SELECT first and compare counts: five rows in, three expected out, two deleted. Take a backup or do it inside a transaction you can roll back. Then add a unique constraint on the key so the duplicates cannot return: ALTER TABLE customers ADD PRIMARY KEY (customer_id).

Look at the duplicates before removing them

Do not skip the look. Duplicates are data, and they sometimes tell you something.

SELECT customer_id, count(*) AS n, min(updated_on) AS first_seen, max(updated_on) AS last_seen
FROM customers
GROUP BY customer_id
HAVING count(*) > 1
ORDER BY n DESC;

If a handful of customers have two or three rows, you are looking at history or re-imports. If every customer has exactly two, a join or an import ran twice. If duplicates share the key but differ in meaning (two different people with the same ID), the key is wrong and removal destroys data. In Excel, =COUNTIF(Customers[customer_id], [@customer_id]) in a helper column, filtered to values above 1, shows the same (finding duplicate rows in an Excel sheet).

Check what differs between duplicates too. The three rows for Acme differ in email and name. Keeping the latest assumes the latest is the best, which holds for "last known address" and fails for "the most complete record". If the better row is the one with the most filled fields, rank on that instead.

Keep the raw data

Deleting duplicates is irreversible. A more durable design keeps all rows and defines the deduplicated set as a view:

CREATE VIEW customers_current AS
SELECT DISTINCT ON (customer_id) *
FROM customers
ORDER BY customer_id, updated_on DESC, name;

Reports read the view; the history stays available. In Excel the same idea is a raw sheet that nobody edits and a derived sheet built by the formulas above.

Check the result

Count the rows: the deduplicated count must equal the number of distinct keys (count(DISTINCT customer_id), here 3). Check that every key appears once. Spot check the kept row for the key with the most duplicates, and compare against the maximum date. Check for NULL or blank keys: DISTINCT ON and GROUP BY treat all NULLs as one group, and Excel's Remove Duplicates treats blanks as a single value, so every row without a key collapses into one. Decide what to do with rows that have no key at all, because they are rarely duplicates of each other.

When removing duplicates is the wrong fix

Removing duplicates is the wrong fix when the duplicates are the symptom of a bad join or a double import: remove the cause. It is wrong when rows differ meaningfully and the key does not capture the identity, such as people sharing an email address. It is wrong when you need an audit trail, because history is exactly what duplicates are. For a clean treatment of the identity question, see finding duplicate rows in an Excel sheet, and for the PostgreSQL syntax, the manual's SELECT reference on DISTINCT ON and Microsoft's page on removing duplicate values.