CODEWITHSUNDEEP
sqlsql-servert-sqlhaving-clause
Yana
Yana

Reputation: 975

Return rows where specific column has duplicate values

From the table below I want to show the two rows where the values in column 3 are duplicates:

ID Col2 Col3
1 a 123
2 b 123
3 c 14
4 d 65
5 e 65

This means that the query that I need should return rows with ID 1, 2 and 4, 5. I wrote query using having:

SELECT *
FROM t1
INNER JOIN (SELECT col3 FROM t1
            GROUP BY col3
            HAVING COUNT(*) > 1) a
ON t1.col3 = a.col3

This query though only returns 1 and 4 rows for example, not all duplicates. I would appreciate the help.

Upvotes: 0

Views: 1443

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Your query should work, but I would suggest window functions:

select t1.*
from (select t1.*, count(*) over (partition by col3) as cnt
      from t1
     ) t1
where cnt > 1;

Upvotes: 1

Related Questions