Cannot select unique rows(based on few columns) from a table and having one column computed based on-CodePudding

I have a legacy table with data like below, I am trying to select unique records with one column to be computed based on the values of records which are considered to be duplicate :

Please note for simplicity, I have taken values of Is_hit and Is_recvd as same but it might be different.

  Case     Source  Is_Hit     Is_recvd
27887024    MHC     null        null
27887024    MHC     true        true    
27887024    DSC     true        true
27887024    CSD     null        null
27886949    DSC     null        null    
27886949    DSC     true        null
27884761    MHC     true        true
27884761    MHC     true        true

A record is said to be unique based on the combination of (Case Source).

Is_hit should be calculated as : Combination of true and null should result in true Combination of true and true should result in true Combination of null and null should result in null

Is_recvd should be calculated(logic is same as is_hit is calculated) as : Combination of true and null should result in true Combination of true and true should result in true Combination of null and null should result in null

Now for the above case the expected result is :

  Case     Source  Is_Hit     Is_recvd
27887024    MHC     true        true
27887024    DSC     true        true
27887024    CSD     null        null    
27886949    DSC     true        null    
27884761    MHC     true        true

Note : All columns are considered to be of type varchar.

CodePudding user response：

Simple aggregation with a MAX function will do the job, try the following:

SELECT [case], Source, MAX(Is_Hit) Is_Hit, MAX(Is_recvd) Is_recvd
FROM table_name
GROUP BY [case], Source
ORDER BY [case] DESC, Source DESC

See a demo.