CODEWITHSUNDEEP
sql-server
MogulBomb
MogulBomb

Reputation: 185

Sql result return value when one of the rows has a certain value

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

Answers (1)

Sean Lange
Sean Lange

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

Related Questions