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
  • 704,970
  • 168
  • 1,303
  • 1,425
Alex Gordon
  • 54,010
  • 276
  • 644
  • 1,024

2 Answers2

17

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
  • 132,594
  • 21
  • 199
  • 273
5

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
  • 790
  • 5
  • 13
  • 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