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.
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;
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).
