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 I can de-conflict 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 I need. With the below I am only getting one occurrence. I need all occurances with duplicate employee_id like above.

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

Answer 1

You could use windowed 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 simple 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