CODEWITHSUNDEEP
sqlsql-serverfiltergroup-bydistinct
Microsoft DN
Microsoft DN

Reputation: 10010

Filter by grouping two columns

ID  Type    Status  
------------------------
1   Type1   Success 
1   Type1   Fail    
1   Type2   Fail    
2   Type3   Fail    
3   Type1   Success

I have above data

I want to filter this data group by Id and Type

For example, if there are multiple records for Id 1 and type1, I want to show only one record of this combination (irrespective of the status).

ID  Type    Status  
------------------------
1   Type1   Success 
1   Type2   Fail    
2   Type3   Fail    
3   Type1   Success

I tried using distinct and group by, but not getting proper result. (there are some other columns as well in this table) This musu be very simple but I am not able to do it.

Any help would be appreciated.

Upvotes: 0

Views: 33

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use ROW_NUMBER for this:

SELECT ID, Type, Status, ... rest of the fields here
FROM (
  SELECT ID, Type, Status, ... rest of the fields here, 
         ROW_NUMBER() OVER (PARTITION BY ID, Type 
                            ORDER BY Status) AS rn
  FROM mytable) t
WHERE t.rn = 1

This will pick the record having the minimum Status value within the ID, Type partition.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use aggregation. If you don't care about status, just use min():

select id, type, min(status) as status
from t
group by id, type;

If "don't care" really means "random", then use row_number() instead:

select id, type, status
from (select t.*,
             row_number() over (partition by id, type order by newid()) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions