Re: [firebird-support] How do find duplicates in a table?

 

If PERSON_ID is unique and you have an index on SOC_SEC_NO, you can find all records with duplicate SOC_SEC_NO this way (ordered by SOC_SEC_NO, so that they are kind of grouped together):

SELECT *

FROM PERSON P

WHERE EXISTS(SELECT * FROM PERSON P2

             WHERE P.PERSON_ID <> P2.PERSON_ID

                  AND P.SOC_SEC_NO = P2.SOC_SEC_NO)

ORDER BY P.SOC_SEC_NO

(well, you can do it without an index as well, but then it will be slow if PERSON contains millions of records)

If you want to delete the duplicates, you can e.g. do (this will delete all duplicates, except the one with the lowest PERSON_ID):

DELETE FROM PERSON P

WHERE EXISTS(SELECT * FROM PERSON P2

             WHERE P.PERSON_ID > P2.PERSON_ID

                  AND P.SOC_SEC_NO = P2.SOC_SEC_NO)

2016-02-04 20:09 GMT+01:00 'stwizard' stwizard@att.net [firebird-support] <firebird-support@yahoogroups.com>:

Greetings All,

 

How do I form a SQL Select statement that will return which records in my PERSON table have duplicate SOC_SEC_NO.

 

In other words I need a list of persons where the social security number appears in the database more than once.  Some SOC_SEC_NO may be null which I do not care about.

 

PERSON:

PERSON_ID

SOC_SEC_NO

 

Any help appreciated,

Mike

__._,_.___

---

Posted by: =?UTF-8?Q?Svein_Erling_Tysv=C3=A6r?= <setysvar@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (9)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 3

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___