CODEWITHSUNDEEP
sqlsql-servert-sql
Divya
Divya

Reputation: 9

Fetch duplicate rows from a table

I have the following table 

Col1 | Col2
2    | jim
2    | jam
3    | raw
3    | cooked
3    | boiled
5    | none
6    | yum

So in this table I want to fetch records which have multiple value in col1 like: 

Col1 | Col2
2    | jim
2    | jam
3    | raw
3    | cooked
3    | boiled

Upvotes: 0

Views: 38

Answers (3)

Tanveer Badar
Tanveer Badar

Reputation: 5524

Use this query

select *
from t
where col1 in
( select col1
  from t
  group by col1
  having count(*) > 1
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use exists:

select col1, col2
from t
where exists (select 1
              from t t2 
              where t2.col1 = t.col1 and t2.col2 <> t.col2
             );

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

This should work for you, an alternative to using EXISTS:

select t.*
from <table> t
   cross apply (select 1 ex
                from <table> t2 
                where t2.Col1=t.Col1 
                group by t2.Col1 
                having count(t2.Col1) > 1) tmp

Upvotes: 1

Related Questions