locked
Special Group by RRS feed

  • 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

    Monday, September 21, 2020 7:34 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 22, 2020 2:45 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 22, 2020 2:45 AM
  • User1623084388 posted

    Thank you XuDong Peng

    Tuesday, September 22, 2020 1:36 PM