rows with duplicate column values

[fixthebug2003]

HI,
I have a table tab1...one of its column is say col1. I need to find all the records that have the duplicate col1 values..

how to write the SQL quesry for the same

Fixthebug2003

 

[PHV]

Something like this ?
SELECT A.* FROM tab1 A INNER JOIN
(SELECT col1 FROM tab1 GROUP BY col1 HAVING COUNT(*)>1) B
ON A.col1 = B.col1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JarlH]

PHV's solution above works perfect, but it is using the SQL standard extension "Derived tables". If your DBMS doesn't support it derived tables, you can try the following Core SQL-99 compliant solution:

SELECT * FROM tab1 WHERE col1 IN
(SELECT col1 FROM tab1 GROUP BY col1 HAVING COUNT(*) > 1)