0

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.

2 Answers 2

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;
1
  • It woked for me, thanx for the help
    – Adi
    Nov 15, 2018 at 9:37
0

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         |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.