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.

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;
  • It woked for me, thanx for the help – Adi Nov 15 '18 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, privacy policy and cookie policy

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