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' stwiz...@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 > > > >
RE: [firebird-support] How do find duplicates in a table?
Also, if you want to get back the PERSON records instead of the SOC_SEC_NO try. WITH CTE AS (SELECT SOC_SEC_NO FROM PERSON GROUP BY SOC_SEC_NO HAVING COUNT(*) > 1) SELECT PERSON.* FROM PERSON INNER JOIN CTE ON (CTE.SOC_SEC_NO = PERSON.SOC_SEC_NO) Hope this is useful, Edward From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, February 4, 2016 3:09 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] How do find duplicates in a table? Thanks Woody much simpler. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, February 04, 2016 1:42 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do find duplicates in a table? Try this instead: Select Soc_Sec_No, count(*) from Person group by Soc_Sec_No having count(*) > 1 This will list the social security numbers and the count if there are more than one without all the additional selects. HTH Woody (TMW)
Re: [firebird-support] How do find duplicates in a table?
04.02.2016 20:09, 'stwizard' stwiz...@att.net [firebird-support] wrote: > How do I form a SQL Select statement that will return which records in my > PERSON table > have duplicate SOC_SEC_NO. RTFM GROUP BY, HAVING, COUNT(). -- WBR, SD. ++ 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/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] How do find duplicates in a table?
I had finally figured it out just before your reply SELECT DISTINCT P.SOC_SEC_NO, (SELECT COUNT(*) FROM PERSON P2 WHERE P2.SOC_SEC_NO = P.SOC_SEC_NO) AS CNT FROM PERSON P WHERE P.SOC_SEC_NO IS NOT NULL GROUP BY 1 HAVING (SELECT COUNT(*) FROM PERSON P3 WHERE P3.SOC_SEC_NO = P.SOC_SEC_NO) > 1 This appears to work. See anything that I should change? -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, February 04, 2016 1:25 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do find duplicates in a table? 04.02.2016 20:09, 'stwizard' stwiz...@att.net [firebird-support] wrote: > How do I form a SQL Select statement that will return which records in > my PERSON table have duplicate SOC_SEC_NO. RTFM GROUP BY, HAVING, COUNT(). -- WBR, SD.
[firebird-support] How do find duplicates in a table?
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
Re: [firebird-support] How do find duplicates in a table?
Try this instead: Select Soc_Sec_No, count(*) from Person group by Soc_Sec_No having count(*) > 1 This will list the social security numbers and the count if there are more than one without all the additional selects. HTH Woody (TMW) -Original Message- From: 'stwizard' stwiz...@att.net [firebird-support] Sent: Thursday, February 04, 2016 1:37 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] How do find duplicates in a table? I had finally figured it out just before your reply SELECT DISTINCT P.SOC_SEC_NO, (SELECT COUNT(*) FROM PERSON P2 WHERE P2.SOC_SEC_NO = P.SOC_SEC_NO) AS CNT FROM PERSON P WHERE P.SOC_SEC_NO IS NOT NULL GROUP BY 1 HAVING (SELECT COUNT(*) FROM PERSON P3 WHERE P3.SOC_SEC_NO = P.SOC_SEC_NO) > 1 This appears to work. See anything that I should change? -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, February 04, 2016 1:25 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do find duplicates in a table? 04.02.2016 20:09, 'stwizard' stwiz...@att.net [firebird-support] wrote: > How do I form a SQL Select statement that will return which records in > my PERSON table have duplicate SOC_SEC_NO. RTFM GROUP BY, HAVING, COUNT(). -- WBR, SD. Posted by: "stwizard" <stwiz...@att.net> ++ 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/ ++ Yahoo Groups Links
Re: [firebird-support] How do find duplicates in a table?
Hi, It is faster to use RDB$DB_KEY to find and delete duplicates. See more details here http://ib-aid.com/en/articles/how-to-find-and-delete-duplicate-records-in-firebird-database/ Regards, Alexey Kovyazin IBSurgeon I had finally figured it out just before your reply SELECT DISTINCT P.SOC_SEC_NO, (SELECT COUNT(*) FROM PERSON P2 WHERE P2.SOC_SEC_NO = P.SOC_SEC_NO) AS CNT FROM PERSON P WHERE P.SOC_SEC_NO IS NOT NULL GROUP BY 1 HAVING (SELECT COUNT(*) FROM PERSON P3 WHERE P3.SOC_SEC_NO = P.SOC_SEC_NO) > 1 This appears to work. See anything that I should change? -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, February 04, 2016 1:25 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do find duplicates in a table? 04.02.2016 20:09, 'stwizard' stwiz...@att.net [firebird-support] wrote: > How do I form a SQL Select statement that will return which records in > my PERSON table have duplicate SOC_SEC_NO. RTFM GROUP BY, HAVING, COUNT(). -- WBR, SD.
RE: [firebird-support] How do find duplicates in a table?
This may works. SELECT PERSON.PERSON_ID, COUNT(PERSON.SOC_SEC_NO) FROM PERSON HAVING COUNT(PERSON.SOC_SEC_NO) > 1; From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: quinta-feira, 4 de fevereiro de 2016 17:10 To: firebird-support@yahoogroups.com Subject: [firebird-support] How do find duplicates in a table? 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
RE: [firebird-support] How do find duplicates in a table?
Thanks Woody much simpler. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, February 04, 2016 1:42 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] How do find duplicates in a table? Try this instead: Select Soc_Sec_No, count(*) from Person group by Soc_Sec_No having count(*) > 1 This will list the social security numbers and the count if there are more than one without all the additional selects. HTH Woody (TMW)
