Skip to content
Advertisement

SQL How to filter table with values having more than one unique value of another column

I have data table Customers that looks like this:

ID | Sequence No |
1  |  1          |
1  |  2          |
1  |  3          |
2  |  1          |
2  |  1          |
2  |  1          |
3  |  1          |
3  |  2          |

I would like to filter the table so that only IDs with more than 1 distinct count of Sequence No remain.

Expected output:

ID | Sequence No |
1  |  1          |
1  |  2          |
1  |  3          |
3  |  1          |
3  |  2          |

I tried

select ID, Sequence No 
from Customers 
where count(distinct Sequence No) > 1
order by ID

but I’m getting error. How to solve this?

Advertisement

Answer

You can get the desired result by using the below query. This is similar to what you were trying –

Sample Table & Data

Declare @Data table
(Id int,  [Sequence No] int)

Insert into @Data
values
(1  ,  1   ),
(1  ,  2   ),
(1  ,  3   ),
(2  ,  1   ),
(2  ,  1   ),
(2  ,  1   ),
(3  ,  1   ),
(3  ,  2   )

Query

Select * from @Data 
where ID in(
            select ID
            from @Data 
            Group by ID
            Having count(distinct [Sequence No]) > 1
            )
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement