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:
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?