CODEWITHSUNDEEP
sqloracle-database
Ravindra S
Ravindra S

Reputation: 49

Select duplicate rows in multiple columns of a table

I have a table with 4 columns, i would like to select the rows for columns 1, 2 and 3 have same data.

TableName
Col1  Col2  Col3   Col4
abc   12    xyz     1
abc   12    xyz     2
abc   12    xyz     3
abc   12    xyz     4
def   34    wxy     5
ghi   45    uvw     6
ghi   45    uvw     7
ijk   45    tuv     8

I'm expecting to select the rows 1 to 4 and 6,7

Thanks.

Upvotes: 0

Views: 84

Answers (2)

peter.petrov
peter.petrov

Reputation: 39477

One possible way of doing it is as follows. 

select distinct t1.* 
from TableName t1
left join TableName t2
on t1.Col4 <> t2.Col4 and t1.Col1 = t2.Col1 and t1.Col2 = t2.Col2 and t1.Col3 = t2.Col3 
where
t2.Col4 is not null

This is not very optimal though (if your table has lots of rows and lots of duplicates).

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you are trying to get just lists of such values:

select col1, col2, col3
from tablename
group by col1, col2, col3
having count(*) > 1;

If you want the actual rows, then join this back in:

select tn.*
from tablename tn join
     (select col1, col2, col3
      from tablename
      group by col1, col2, col3
      having count(*) > 1
     ) tncol
     on tn.col1 = tncol.col1 and tn.col2 = tncol.col2 and tn.col3 = tncol.col3;

Upvotes: 2

Related Questions