Skip to content
Advertisement

Filter and keep most recent duplicate

Please help me with this one, I’m stuck and cant figure out how to write my Query. I’m working with SQL Server 2014.

Table A (approx 65k ROWS) CEID = primary key

Desired output

I want to keep the row with highest CEID if “state” is equal for all duplicate checksums. If state differs but Checksum is equal i want to keep the row with highest CEID for State=9. Unique rows like CEID 9 and 10 should be included in result regardless of State.

This join returns all duplicates:

I’ve also identified MAX(CEID) for each duplicate checksum with this query

With the first query, I can’t figure out how to SELECT the row with the highest CEID per Checksum.

The problem I encounter with last one is that GROUP BY isn’t allowed in subqueries when I try to join on it.

Advertisement

Answer

You can use row_number() with partition by checksum and order by State desc and CEID desc. Note both of your condition may be fulfill by ORDER BY State desc, CEID desc

And take the first row_number

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement