select single row from table for a particular column combination

declare @t table(Id int, ColA int, ColB int, Isdefault bit); insert into @t values (1, 100, 96, 1), (2, 100, 96, 0), (3, 101, 98, 0), (4, 102, 99, 1); with cte as ( select *, row_number() over(partition by ColA, ColB order by Isdefault desc) rn from @t ) select Id, ColA, ColB, Isdefault from cte where rn = 1; 

Assuming that every pair of (ColA,ColB) has only two possible values (0,1), you can use an ANTI SEMI JOIN:

SELECT Id, ColA, ColB, Isdefault FROM @t t1 WHERE Isdefault = 1 OR (NOT EXISTS (SELECT 1 -- Implicit Isdefault=0 here FROM @t t2 WHERE t2.ColA = t1.ColA AND t2.ColB = t1.ColB AND Isdefault = 1)); 

| Id | ColA | ColB | Isdefault | |----|------|------|-----------| | 1 | 100 | 96 | 1 | | 3 | 101 | 98 | 0 | | 4 | 102 | 99 | 1 |