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:

    ID    |  Sub-id  | VALUE
  ------------------------------
     1    |     1    |  "T"
  ------------------------------
     1    |     1    |  "F"
  ------------------------------
     1    |     2    |  "Z"
  ------------------------------
     1    |     2    |  "G"

The desired output is:

    ID    |  Sub-id  | VALUE
  ------------------------------
     1    |     1    |  "T"
  ------------------------------
     1    |     1    |  "F"
  ------------------------------

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

    ID    |  Sub-id  | VALUE
  ------------------------------
     1    |     2    |  "Z"
  ------------------------------
     1    |     2    |  "G"

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:

 WITH CTE (ID,SUB-ID,DuplicateCount,VALUE) AS (
    SELECT 
        ID,
        Sub-id,
        ROW_NUMBER () OVER (PARTITION BY ID
        ORDER BY SUB-ID) as DUPLICATECOUNT,
        VALUE
    FROM TBL_SOURCE

What am I missing out?

Answer

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

select *
from (
    select t.*, rank() over(partition by id order by subid) rn
    from mytable t
) t
where rn = 1

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.

Advertisement

Answer