Tuesday, March 27, 2012

SQL to find duplicates - Sample PS_PERS_NID

Here is a sample SQL of how one could find multiple rows.

Example: Using HAVING count(*) > 1

SELECT A.EMPLID,
COUNT(A.EMPLID) AS NumOccurrences
FROM PS_PERS_NID A
GROUP BY A.EMPLID
HAVING ( COUNT(A.EMPLID) > 1 )


All rows within PS_PERS_NID should have a primary row.
Adding primary

SELECT A.EMPLID,
COUNT(A.EMPLID) AS NumOccurrences
FROM PS_PERS_NID A
WHERE A.PRIMARY_NID = 'Y'
GROUP BY A.EMPLID
HAVING ( COUNT(A.EMPLID) > 1 )