Counting & deleting duplicate records from a table

[chica3578]

Hello,

I have a table with multiple columns. The problem is that my DUNS# column has duplicate records with the same number. How can I build a query that counts the # of times each duplicate record shows up. If I wanted to delete the duplicates and keep only one, how would I do so and keep only the record that has the highest Revenue (another column) value associated with it?

Thanks in advance.

-r-

 

[lespaul]

SELECT PRIMARYKEYFIELD, COUNT([DUNS#]) FROM TABLENAME GROUP BY PRIMARYKEYFIELD HAVING COUNT([DUNS#]) > 1

I'll have to think about the delete query

Leslie

 

[r937]

actually, lespaul, if the PRIMARYKEYFIELD is really a primary key field, it will have unique values, so if you GROUP BY it, then there will only ever be one row per value

i think what you meant was

[tt]select [duns#], count(*)
from thetable
group
by [duns#]
having count(*) > 1[/tt]

as for the delete process, do this --

[tt]select PRIMARYKEYFIELD
into keepthese
from thetable XX
where Revenue =
( select max(Revenue)
from thetable
where [duns#] = XX.[duns#] )[/tt]

then

[tt]delete from thetable
where PRIMARYKEYFIELD not in
( select PRIMARYKEYFIELD
from keepthese )[/tt]

note that if two rows with the same [duns#] have the same maximum revenue, they will both be kept

rudy

http://r937.com/

 

[chica3578]

Thank you so very much. I will try those queries tomorrow. I appreciate your help.

 

[lespaul]

i'm like 0 for 3 tonight!! Guess it was the brain drain from the all day employee conference! [sarcasm]What fun![/sarcasm]

Leslie