Duplicate Records

[LouisC4]

Hi,

How can I use an SQL - SELECT statement to ONLY list duplicate records?

if I do:

SELECT name1 DISTINCT, gender, jobdesc;
FROM clients INTO CURSOR tmpcursor

This will give me a list of all none duplicate names. What I am looking for are all the names that are duplicates (the ones that do repeat).

Thanks,

Louis

 

[Mike Gagnon]

SELECT CNT(*) as cnt, jobnum from mytable GROUP BY jobnum HAVING cnt(*) > 1


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[mkrausnick]

Slight correction to Mike G's example, and using your table and columns:

SELECT name1,CNT(*) as cnt from clients GROUP BY name1 HAVING [red]count(*)[/red] > 1

-or-

SELECT name1,CNT(*) as cnt from clients GROUP BY name1 HAVING [red]cnt[/red] > 1

That is, assuming they are Foxpro tables. If using SQL Server tables, only the first form works.


Mike Krausnick
Dublin, California

 

[thatguy]

I use the following to check for dups in one field.. maybe you can play around and get it to work for you.. replace the italics with your table/fields.. this relies on the table having a primary key field..

SELECT * FROM [i]tablename[/i] WHERE [i]tablename.targetfield[/i] IN ;
  (SELECT [i]targetfield[/i] FROM [i]tablename[/i] AS usedagain WHERE usedagain.[i]primarykeyfield[/i] # [i]tablename.primarykeyfield[/i])

hth
--frank~

 

[LouisC4]

Thanks all, they all work.