CODEWITHSUNDEEP
sqlsql-server-2008t-sql
M99
M99

Reputation: 1907

SQL Server - Query to return groups with multiple distinct records

My table:

Col1    Col2
1       xyz
1       abc
2       abc
3       yyy
4       zzz
4       zzz

I have a table with two columns. I want to query for records where col1 has more than one DISTINCT col2 values. In the example table given above, the query should return records for col1 with value "1".

Expected query result: 

Col1    Col2 
1       xyz 
1       abc

Upvotes: 1

Views: 124

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

select t.col1, t.col2
from (
  select col1
  from tbl
  group by col1
  having MIN(col2) <> MAX(col2)
) x
join tbl t on t.col1 = c.col1

Upvotes: 1

John Woo
John Woo

Reputation: 263693

SELECT *
FROM tableName
WHERE Col1 IN
(
    SELECT Col1
    FROM tableName
    GROUP BY Col1
    HAVING COUNT(DISTINCT col2) > 1
)

SQLFiddle Demo

Upvotes: 4

Related Questions