De-dupe records based on Rank

Dom 646 Reputation points

I have a table of customers where they are assigned a category based on particular behavior. It is possible that a customer can be assigned multiple categories (and thus have multiple records in the table). The categories are assigned a Rank in an hierarchy. So if a customer falls into 2 categories and one is assigned Rank #1 and the other is assigned Rank #3, I only want to keep the record that has the Rank #1 category. Here is an example of data:

User's image

In the above example, the first customer was assigned two categories. I would only want to keep the second record with Rank#1 and delete the other record. How can this be accomplished?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,034 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 88,856 Reputation points

    As I understand it, you want to keep the row with the lowest rank for each customer.

    ; WITH CTE AS (
        SELECT row_number() OVER(PARTITION BY ACCOUNTID ORDER BY rank) AS rowno
        FROM   tbl
    DELETE CTE WHERE rowno > 1