Board index » delphi » Finding duplicate records

Finding duplicate records

Quote
Connie Okiro wrote:

> I am trying to figure out a way to extract duplicate detail records from
> Local Interbase 4.1.  I have tried to do a 'Select * from TEST where
> count(*) > 1', and get invalid aggregate function messages.

> Can anyone point me in the right direction?

> Connie

That's a big question actually. It depends on whether you want all dupes
( the duplicates and the duplicatee ) or just the dupes..

1 Smack
2 Smith
3 Smith
4 Smith
5 Smote

You want 2,3 & 4 or just 3 & 4, or perhaps 2 & 4?

Rob

 

Re:Finding duplicate records


I am trying to figure out a way to extract duplicate detail records from
Local Interbase 4.1.  I have tried to do a 'Select * from TEST where
count(*) > 1', and get invalid aggregate function messages.

Can anyone point me in the right direction?

Connie

Re:Finding duplicate records


Hi Connie

I am trying to figure out a way to extract duplicate detail records from

Quote
> Local Interbase 4.1.  I have tried to do a 'Select * from TEST where
> count(*) > 1', and get invalid aggregate function messages.

Say, do you want to do that in the future or do you want to process an
existing database? The first one is rather simple, the latter is quite
complex. Anyway, you have to figure out, which field you consider to qualify
as a decision basis for being a doublette.

Martin

--
MindPower
EDV-Dienste
______________________________________________________
 Internet-Mail:    martin.b...@mindpower.com
 Internet (biz):   http://www.mindpower.com
 Internet (priv):  http://www.mindpower.com/mb-es
 CompuServe:       75300,22
 PGP-Key:          http://www.mindpower.com/mb-es/pgp.htm
 Voice:            +41-1-687 11 66
______________________________________________________
 Visit our Flea Market! Place your ad for free! Edit
 or delete it any time.
 http://www.mindpower.com/flohmarkt.htmls
______________________________________________________

Re:Finding duplicate records


Connie Okiro <Ok...@compuserve.com> wrote in article
<01bcebf1$8a981680$7aafaec7@itjfvkli>...

Quote
> I am trying to figure out a way to extract duplicate detail records from
> Local Interbase 4.1.  I have tried to do a 'Select * from TEST where
> count(*) > 1', and get invalid aggregate function messages.

I have a table ContactMain in SQL Anywhere database, with 2 fields
FirstName and Surname
To select duplicates the following works :

SELECT FirstName, Surname, Count( * ) AS Cnt
FROM ContactMain
GROUP BY FirstName, Surname
HAVING Cnt > 1

This is standard Transact SQL, so it should work for Interbase

Hope this helps

Regards,
Warren van Niekerk <senti...@icon.co.za>
Sentient Software
Software that thinks for itself

Re:Finding duplicate records


Quote
> That's a big question actually. It depends on whether you want all dupes
> ( the duplicates and the duplicatee ) or just the dupes..

Never mind, I figured it out.  I wanted fully duplicated records.  I just
forgot to add the Group By... and Having...statements to my Query (duh, my
name is ... umm,...umm,)

Connie

Re:Finding duplicate records


Quote
> Say, do you want to do that in the future or do you want to process an
> existing database? The first one is rather simple, the latter is quite
> complex. Anyway, you have to figure out, which field you consider to
qualify
> as a decision basis for being a doublette.

Never mind, I figured it out (gee, don't you just hate that...Asking a
question, then realizing the answer about 5 minutes later...?)

Connie

Re:Finding duplicate records


Quote
> I have a table ContactMain in SQL Anywhere database, with 2 fields
> FirstName and Surname
> To select duplicates the following works :

> SELECT FirstName, Surname, Count( * ) AS Cnt
> FROM ContactMain
> GROUP BY FirstName, Surname
> HAVING Cnt > 1

It does...I figured that out shortly after I posted the question.  (Its
amazing how much help there is out there, when I'm being spacey)

Connie

Other Threads