Reputation: 185
I have a query that is returning some data that looks similar to this:
Select DISTINCT ID_NUM, CRS_ID, ACTION FROM MY_TABLE
The data returned looks like this:
ID Num | CRS_ID | ACTION
1111111|ABC_DEF | C
1111111|ABC_DEF | D
1111111|GHI_JKL | C
2222222|ABC_DEF | C
2222222|GHI_JKL | C
I need to manipulate the data so that in the event that there is more than one row for a ID_NUM and CRS_ID combo it will only return one row with a 'C' in the event there are multiple action values. If there is only one value it should just remain as it is.
Upvotes: 1
Views: 55
Reputation: 33581
Here is one way to do this using ROW_NUMBER.
select ID_NUM
, CRS_ID
, ACTION
from
(
Select ID_NUM
, CRS_ID
, ACTION
, ROW_NUMBER() over(partition by ID_NUM, CRS_ID order by case when Action = 'C' then 1 else 2 end) as RowNum
FROM MY_TABLE
) x
where x.RowNum = 1
Upvotes: 2