Subject Re: [Firebird-general] Need help finding duplicate values
Author Aage Johansen
matt_kopf wrote:
> I am trying to set a primary key on a table that is already popluated.
> The table has more than 5000 records in it, and when I set the primary
> key I get an error that says there are duplicate values in the table.
> What I need is a sql statement that will show me what records in the
> table are duplicated. This is what I have come up with so far in my
> testing
> select a.* from idptodn a, idptodn b
> where (a.tdstamp = b.tdstamp);
> but it returns all the records as well as the duplicate ones. I am
> very new at SQL so the reason for this is a Mystery to me! The script
> above is just a sample that I was testing with. In the real table
> there are 6 fields and 5 of them are for the new primary key. Any help
> would be great!!!

There are several ways to skin this cat, but ottomh:
select PK1, PK2, PK3, PK4, PK5, count(*)
group by PK1, PK2, PK3, PK4, PK5
having count(*) > 1
will show which ones need attention.

Aage J.