Remove SQL Duplicates with ROW_NUMBER

What this solves

Keep the latest scan per HU. This pattern is useful when the business question is clear but a generic tutorial is too abstract.

The rule

Partition by the duplicate key and order by the row you want to keep.

WITH x AS (
 SELECT *, ROW_NUMBER() OVER(PARTITION BY HU ORDER BY ScanTime DESC) rn
 FROM Scans
)
SELECT * FROM x WHERE rn=1;

Real-work checklist

1. Define the business key and expected grain before writing the formula, query or markup.
2. Test the pattern on a small known sample where you can verify the answer manually.
3. Check missing values, duplicates and data types before trusting a large result.
4. Scale to the real file or database only after the logic is proven.

Try it next