Special Group by

Question

User1623084388 posted

Hi everyone,

I have the table below, and I am trying to list all the duplicate codes with distinct val or all val = 0

Code	Val
1	0
1	0
2	0
2	1
3	1
3	1
4	1
4	0
5	0
6	1

declare @Test table (Code varchar(10), Val bit)
Insert into @Test values
 ('1',0)
,('1',0)
,('2',0)
,('2',1)
,('3',1)
,('3',1)
,('4',1)
,('4',0)
,('5',0)
,('6',1)

The result should be:

Code

1

2

4

Thanks

Answer 1

User-939850651 posted

Hi psarr,

You could try to use inner join to achieve your requirements, something like this:

select distinct X.Code 
from (
	select  Code from CodeVal  group by Code having count(Code)>1
) as X 
	inner join CodeVal on x.Code=CodeVal.Code and CodeVal.Val=0

Result:

Hope this can help you.

Best regards,

Xudong Peng

Answer 2

User1623084388 posted

Thank you XuDong Peng