How to delete only one of a set of duplicates

• Edvard Korsbæk

  SSCertifiable

  Points: 5991

  More actions

  November 22, 2012 at 2:14 pm

  #271142

  I have made some mistakes in my programming, ending op in a 1:many, where one of the 'Many' is dulicated.

  I want to make a script, that corrects this - Have been trying a coupple of days, but not finding out anything.

  I have the table dutyrostershift, whis has a field ID (INT)

  I have a table TimeAccountMovement, which has several fields, but two interesting here.

  Ownerid (int)

  TimeAccountID (int)

  the bind between the two tables is timeaccountmovement.ownerid = dutyrostershift.id

  there can be many timeaccountmovements corrssponding to the dutyrostershift, but there should be only one of each Timeaccountmovement.TimeAccountID

  Problem is, that i have made several here and there.:blush:

  They are always identical meaning that it don't make any difference which is deleted.

  But 'how to' is the big question

  What should I look at?

  Best regards

  Edvard Korsbæk

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  November 22, 2012 at 3:44 pm

  #1561720

  I'm sorry, but I got lost in the explanation. Could you read the article linked in my signature and come back to post DDL, sample data and expected results? (it's all explained in the article).

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 22, 2012 at 4:11 pm

  #1561725

  Like Luis Cazares - has said

  But here is some T-SQL that you should be able follow and experiment with (USING tempdb of course) and with modifications make it the tool you need to solve your particular problem.

  CREATE TABLE #T(Date_Stamp DATETIME,KW1 DECIMAL(5,1), KW2 DECIMAL(5,1))

  INSERT INTO #T

  SELECT '12/10/2010', 5.3, 3.1 UNION ALL

  SELECT '12/10/2010', 5.3, 3.1 UNION ALL

  SELECT '12/9/2010', 4, 2 UNION ALL

  SELECT '12/8/2010', 3, 1 UNION ALL

  SELECT '12/7/2010', 7.4, 5 UNION ALL

  SELECT '12/7/2010', 7.4, 5 UNION ALL

  SELECT '12/7/2010', 7.4, 5

  ;with cte

  as (select row_number() over(partition by Date_Stamp,KW1,KW2 order by Date_Stamp) as rn,

  KW1,KW2

  from #T)

  -- to show complete results:

  SELECT * FROM cte

  /*results:

  rnKW1KW2

  17.45.0

  27.45.0

  37.45.0

  13.01.0

  14.02.0

  15.33.1

  25.33.1

  -- to show only duplicates

  SELECT * FROM cte where ron_number > 1

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  November 22, 2012 at 4:18 pm

  #1561726

  Because you don't care which are deleted, you can use a CTE with a row number:

  Something like this:

  ;WITH dupefinder AS

  (

  SELECT

  OwnerID,

  TimeAccountID,

  ROW_NUMBER() OVER(PARTITION BY OwnerID, TimeAccountID ORDER BY @@SPID) as RowNumber

  FROM Timeaccountmovement

  )

  DELETE

  FROM dupefinder

  WHERE RowNumber>1

  This assigns numbers to each row in the table, restarting at one(1) for every combination of OwnerID and TimeAccountID.

  So, if you have a duplicate, you will get RowNumbers 1 and 2. For a triplicate, you get 1,2,3.

  Then it is simple to delete anything that is not RowNumber 1.

  The "ORDER BY @@SPID" clause is just there because you have to ORDER BY something - in this case you stated that you

  don't care about the order, so I used @@SPID which is constant , you could also use ORDER BY (SELECT NULL) if you prefer.

  {edit: I was slow typing this, and a similar answer has already been posted, but seeing as I typed it, I figured I may as well post it}

  MM
  

  ---

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

• 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]