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;

Lukasz Szozda · Accepted Answer · 2016-04-03 10:21:07Z

60

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

edited Apr 3, 2016 at 10:21

answered Apr 3, 2016 at 10:13

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL to select all rows with duplicate values in one column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related