FInd Duplicate Records 1

[dbinfoweb]

Hello,
I have a table and would to get those duplicate records based on only five columns in the tables. For instance, if column A through E are the same, then they are the records I want to show up. Is there a methodology to handle this? Thanks in advance for any help.

 

[rrrkrishnan]

Try this

select * from tbl_a
where (col_a,col_b,....col e) in
(select col_a,col_b,....col e
from tbl_a
group by 1,2,3,4,5
having count(*)>1)
order by col_a,col_b,....col e

 

[carp]

I don't think the preceding will work since you can't use positional references in the GROUP BY like that. Instead, it should read:

select * from tbl_a
where (col_a,col_b,....col e) in
(select col_a,col_b,....col e
from tbl_a
group by col_a,col_b,....col e
having count(*)>1)
order by col_a,col_b,....col e

 

[srishan]

Why sub-query here?

Just do,

SELECT colA, colB, colC, colD, colE
FROM   table_A
GROUP BY colA, colB, colC, colD, colE
HAVING count(*) > 1;

Thx,
SriDHAR

 

[srishan]

I agree, if we want the whole records, YES!!

Sorry for jumping before reading it through!!

Oopsi!!

Sri

 

[dbinfoweb]

Thank you all so much. This really worked.

select * from tbl_a
where (col_a,col_b,....col e) in
(select col_a,col_b,....col e
from tbl_a
group by col_a,col_b,....col e
having count(*)>1)
order by col_a,col_b,....col e


And that's exactly what I need. I found this forum very helpful. Thanks again!