Return rows where specific column has duplicate values

Question

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.

Answer 1

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;