6

I need to remove duplicates from a table:

;WITH cte as(
SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
                                       ORDER BY ( SELECT 0 ) ) RN
         FROM   quicklabdump)
        delete from cte where RN>1

The column quicklabdumpID is the primary key.

I would like to know how to keep only the largest quicklabdumpID where there are multiple occurrences of [specimen id]

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062

2 Answers2

19

Change your order by to quicklabdumpid DESC.

WITH cte as(
  SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
                            ORDER BY  quicklabdumpid DESC ) RN
  FROM   quicklabdump)
delete from cte where RN>1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
6

No need for partition

delete q
  from quicklabdump q
  where exists
  (
    select *
      from quicklabdump q2
      where q2.[specimen id] = q.[specimen id] and
        q2.quicklabdumpID > q.quicklabdumpID
  )
Clint Good
  • 820
  • 6
  • 14
  • just curious, are you deleting from `quicklabdump` here and @I__ is deleting from the `cte`? – cctan Feb 03 '12 at 02:18
  • @cctan - cte is an alias that is set up using the with statement. – Clint Good Feb 03 '12 at 03:44
  • @ClintGood thank you so much for this. can you please tell me will i need to run this several times if there are more than 2 duplicate [specimen id]s? for example spec123, spec123, and spec123, with quicklabdumpid 1, 2, 3 – Alex Gordon Feb 03 '12 at 04:23
  • @I__ This will do it in one go. As the query says if there is a record that has the same specimen id as a record that has a bigger quicklabdumpID then delete it – Clint Good Feb 03 '12 at 05:48