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' [email protected] [firebird-support]
Sent: Thursday, February 04, 2016 1:37 PM
To: [email protected]
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: [email protected]
[mailto:[email protected]]
Sent: Thursday, February 04, 2016 1:25 PM
To: [email protected]
Subject: Re: [firebird-support] How do find duplicates in a table?
04.02.2016 20:09, 'stwizard' [email protected] [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" <[email protected]>
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
