CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60751

Removing duplicates using partition by SQL Server

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]

Upvotes: 6

Views: 32152

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 19

Clint Good
Clint Good

Reputation: 850

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
  )

Upvotes: 6

Related Questions