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' stwizard@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: Alexey Kovyazin <ak@ib-aid.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

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

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 4

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___