I have a table of IDs and value:

 ID  Value
 X     1
 X     1
 X     2
 Y     5
 Y     5
 Y     5
 Z     3
 Z     6

I want to see which ID contains more than 1 different value. In this case return ID X and Y because X contains[1,2] and Z contains [3,6]:

 ID
 X
 Z

I have tried this:

select ID from
(
    SELECT ID
        ,count(*) over (partition by  [Value]) as c
    FROM mytable
  ) a
  where c>1

But this is not returning the desired answer

CodePudding user response：

Just group them by ID and check wheter it got more than 1 occurrencies in Value field. Something like this

SELECT ID
FROM table
GROUP BY ID
HAVING COUNT(DISTINCT Value) > 1

CodePudding user response：

Try this,

SELECT ID
FROM mytable
GROUP BY ID
HAVING COUNT(DISTINCT Value) > 1;

CodePudding user response：

To answer "I want to see which ID contains more than 1 different value."

SELECT ID, COUNT(Value) ID_count
FROM table
GROUP BY ID, value
HAVING COUNT(Value) > 1

This should give you list of IDs that have multiple values