select single row from table for a particular column combination

Question

I have a table as below

Id... ColA... ColB... Isdefault

---

1... 100... 96... 1
2... 100... 96... 0
3... 101... 98... 0
4... 102... 99... 1

I want to have a select statement, where for a particular combination of ColA and ColB, I should get only one Row with the condition that if Isdefault with values both 0 and 1 present for a particular ColA,ColB combination, I should Only select one row with Isdefault=1 .

The output should look like below:

Id... ColA... ColB... Isdefault

---

1... 100... 96... 1
3... 101... 98... 0
4... 102... 99... 1

Note: Row with Id=2 is not present in the output because the same combination of ColA and ColB exists with isdefault=1

kindly help, thanx in advance.

Answer 1

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;

Answer 2

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         |