How do I delete duplicates in a COUNT result?

Question

Post reply

How do I delete duplicates in a COUNT result?

rnunez

Right there with Babe

Points: 762
More actions
March 4, 2010 at 3:39 pm

#230581

Hi,
I run this query to find duplicate fields(duplicate values in the FieldName column) in this table:
select FieldName from cfg.ExportFieldList group by FieldName
having COUNT(FieldName) > 1
order by FieldName asc
I am getting about 100 records that show me each value that has duplicate.
Can you tell me an easy way to delete the duplicates? I can't seem to figure out how to create a delete statement for this. Let me know if you need more clarification. Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

There is no straightforward answer as we cannot possibly know which ones you want to keep...

You can get the list of them , so you can use that to get you all the duplicate records like this:

select *

from cfg.ExportFieldList

where FieldName in (

select FieldName from cfg.ExportFieldList group by FieldName

having COUNT(FieldName) > 1

)

order by FieldName asc

And if you want to delete ALL of those records you can just do this

DELETE cfg.ExportFieldList

where FieldName in (

select FieldName from cfg.ExportFieldList group by FieldName

having COUNT(FieldName) > 1

)

If you need to keep one of each FieldName value then you would need to review which ones to keep and find some way of excluding them from the delete like this:

DELETE cfg.ExportFieldList

where FieldName in (

select FieldName from cfg.ExportFieldList group by FieldName

having COUNT(FieldName) > 1

)

and (some_key_field <> 'some value')

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

rnunez Right there with Babe Points: 762 More actions · Answer 2

Thanks, I want to keep just one of each value. The only distinct value is the Id the cfg.Exportpdflistid which is a primary key, but looking at your last example, are you saying I would have to specify each unique id? That would take a long time.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

It all depends on how you decide which ones to keep...if you can write a query to select the IDs you want to keep then it is easy to use that in the DELETE.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Without test data to work on this is difficult, but I think you are after something like this:

;WITH

FieldNames AS

( -- this is your list of dupes

SELECT FieldName FROM cfg.ExportFieldList GROUP BY FieldName

HAVING COUNT(FieldName) > 1

),

SingleRowIds AS

( -- this gets the first ID for each dupe group

SELECT FieldName, MIN(Exportpdflistid ) Exportpdflistid

FROM cfg.ExportFieldList

WHERE FieldName IN (

SELECT FieldName FROM FieldNames

)

GROUP BY FieldName

)

-- This deletes the dupes, leaving the rows identified in the SingleRowIds CTE intact

DELETE EFL

FROM cfg.ExportFieldList AS EFL

JOIN FieldNames AS FN ON FN.FieldName = EFL.FieldName

WHERE EFL.Exportpdflistid <> (SELECT TOP 1 SRI.Exportpdflistid FROM SingleRowIds AS SRI WHERE SRI.FieldName = EFL.FieldName)

I MAY HAVE MADE TYPOS AS I CANNOT RUN IT TO TEST SO PLEASE BE VERY CAREFUL! USE A BEGIN TRAN....ROLLBACK TRAN WRAPPER IF POSSIBLE UNTIL YOU ARE 100% SURE ABOUT IT

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);