SQL to select all rows with duplicate values ​​in one column

Question

I have a users table that has duplicate values ​​in the employee_id column. I need to list all rows with duplicate employee_ids along with their names. I need to see all users with a duplicate employee_id so that I can deconflict which values ​​are valid.

 SELECT name,employee_id FROM users; name | employee_id ----------------------- jason 12345 tom 34567 jane 12345 will 01020 bill 12345 

I need to return:

 name | employee_id ------------------------- jason 12345 jane 12345 bill 12345 

I see similar questions, but I still can’t get the correct syntax that I need. From below, I only get one event. I need all events with a duplicate employee_id as above.

 SELECT employee_id, MAX(name) FROM users GROUP BY employee_id HAVING COUNT(employee_id) > 1; 

Answer 1

You can use window COUNT :

 SELECT sub.name, sub.employee_id FROM (SELECT *, COUNT(*) OVER(PARTITION BY employee_id) AS c FROM users) AS sub WHERE c > 1; 

LiveDemo

or just IN :

 SELECT * FROM users WHERE employee_id IN (SELECT employee_id FROM users GROUP BY employee_id HAVING COUNT(employee_id) > 1); 

LiveDemo2

or correlated subquery:

 SELECT name, employee_id FROM users u ,LATERAL (SELECT COUNT(*) FROM users u2 WHERE u.employee_id = u2.employee_id) AS s(c) WHERE c > 1; 

SqlFiddleDemo