Delete duplicates using a SQL Server partition

Question

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 quicklabdumpIDis the primary key.

I would like to know how to save only the largest quicklabdumpID, where there are several occurrences[specimen id]

Answer 1

Change your order 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

Answer 2

No section needed

delete q
  from quicklabdump q
  where exists
  (
    select *
      from quicklabdump q2
      where q2.[specimen id] = q.[specimen id] and
        q2.quicklabdumpID > q.quicklabdumpID
  )