Skip to content
Advertisement

Query to select records belonging only to ID and SUB-ID

I’m trying to construct a query to return only the records related to a given ID and SUB-ID. In case the condition is not met I would like to return the second possibility, i.e, the second combination ID, Sub-ID. Note: The data has thousands of IDs, but only 2 Sub-Ids per ID.

This is an example of input records:

The desired output is:

If we only have records related to the second combination for a given ID, the output should be:

I tried to get this via a CTE expression, but I was not successful. This approach led to returning only one record given my condition.

My try:

What am I missing out?

Advertisement

Answer

If I follow you correctly, you want rank() rather than row_number():

rank() assigns the same number to records that have the same id and subid, as opposed to row_number(), which guarantees the uniqueness of the sequence.

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