How to remove duplicates using window function?

Rafał Gogłoza

Removing duplicates, is a challenging task.

Sometimes you need something special. Using DISTINCT/ GROUP BY / UNION is not enough.

You need to remove duplicates is some other way: using window function:

You can do it using following query:

WITH loc_dim AS (
SELECT 
  ROW_NUMBER() OVER (PARTITION BY u.Location ORDER BY u.Id) AS RowNumber, u.Location
FROM dbo.Users u
)
SELECT loc.Location FROM loc_dim loc
WHERE loc.RowNumber = 1

If you would like to see how input data looks like. What is the expected result. When this approach might not be a good idea. Please continue reading.

Read More