Board index » delphi » Finding duplicate records in a table...

Finding duplicate records in a table...

Hi

I need to find duplicate records in a table in code, and then list them.

Any ideas.

Thanks,

Alex E.

 

Re:Finding duplicate records in a table...


Quote
"alex E" <a...@ed.fiberone.net> wrote:
>Hi

>I need to find duplicate records in a table in code, and then list them.

Alex,

In standard SQL it's possible to find records with duplicate "field"
or column values with this:

        select somecolumn, count(*) from tablename
        having count(*) > 1
        group by somecolumn

Finding whole records that are duplicates is more difficult unless
your database engine has a utility for that.

In theory, there should be no duplicates because every table should
have a unique, primary key. This isn't required in many non-relational
databases, like Paradox, and it still doesn't stop you from having a
name entered twice with different keys.

In that case, the only solid approach is to query the table sorted in
some useful order and then:
        1. scan through the table, one record at a time
        2. put all the field values in local variables. You need two
holders for the field values here. LastRecord and ThisRecord. You can
use record types for this.
        3. Compare this record with values stored from the last record. If
they are all the same, then delete this record.

One caution, though. Deleting records when reading from First to Last
can throw bookmarks off. It's better to save the bookmarks in a local
list and then, after you have scanned the entire table, delete them
from last to first.

Good luck.

Phil Cain
--

Re:Finding duplicate records in a table...


Quote
>I need to find duplicate records in a table in code, and then list them.

>Any ideas.

Something like this

select * from Cows
where key in
(select key from Cows
group by key
Having  Count(Key) >1)
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Finding duplicate records in a table...


Thanks!

Works great...

ALex E

Quote
alex E <a...@ed.fiberone.net> wrote in message

news:82eoqf$gri9@forums.borland.com...
Quote
> Hi

> I need to find duplicate records in a table in code, and then list them.

> Any ideas.

> Thanks,

> Alex E.

Other Threads