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 )