find where column value varies

Question

How can I select only names where the flag value varies for the same name?

name    flag
ben     0
harry   1
harry   1
harry   1
john    1
john    0
john    1
krishna 1
krishna 1
luke    0
steve   1
steve   0

Result should select john and steve

Answer 1

You can use the following which will group the data by the name and count the distinct flag values. If the count is greater than 1 then they have different flags:

select name
from yourtable
group by name
having count(distinct flag) > 1

See SQL Fiddle with Demo

If you want to expand on this and include additional columns in your table, then you could use:

select t1.name, t1.flag
from yourtable t1
where exists (select name
              from yourtable t2
              where t1.name = t2.name
              group by name
              having count(distinct flag) > 1)

See SQL Fiddle with Demo

This will return both the name and the flag:

|  NAME | FLAG |
----------------
|  john |    1 |
|  john |    0 |
|  john |    1 |
| steve |    1 |
| steve |    0 |

Answer 2

Try the following:

SELECT DISTINCT t1.Name
FROM Table t1 
INNER JOIN Table t2 ON t1.Name = t2.Name
WHERE t1.flag <> t2.flag