SQL to select all rows with duplicate values in one column
You could use windowed COUNT
:
SELECT sub.name, sub.employee_idFROM (SELECT *, COUNT(*) OVER(PARTITION BY employee_id) AS c FROM users) AS subWHERE c > 1;
or simple IN
:
SELECT *FROM usersWHERE employee_id IN (SELECT employee_id FROM users GROUP BY employee_id HAVING COUNT(employee_id) > 1);
or correlated subquery:
SELECT name, employee_idFROM users u,LATERAL (SELECT COUNT(*) FROM users u2 WHERE u.employee_id = u2.employee_id) AS s(c)WHERE c > 1;