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?
Advertisement
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.