Delete Duplicate Rows

• Thomas Stringer

  SSCarpal Tunnel

  Points: 4038

  More actions

  August 8, 2011 at 11:30 am

  #242730

  I have seen a few ways to delete duplicate rows, but I can't seem to modify it for my needs.

  Here is what my data is like:

  create table dbo.TestTable

  (

  pk int not null identity(1,1) primary key clustered,

  FirstName varchar(100) null,

  MiddleName varchar(100) null,

  LastName varchar(100) null,

  DateLoaded datetime null

  )

  insert into dbo.TestTable

  values('John', 'Jake', 'Smith', getdate())

  insert into dbo.TestTable

  values('John', 'Jake', 'Smith', getdate()-1)

  insert into dbo.TestTable

  values('Barry', 'Jake', 'Smith', getdate())

  insert into dbo.TestTable

  values('Barry', 'Jake', 'Smith', getdate()-5)

  What I want to do is delete all of the duplicate records that are newer. In other words, the first INSERTed row should be removed because it is the same as the second INSERTed row, but just a newer date.

  I can't really delete based off of the primary key because that's not always a definite that the min primary key was the oldest data.

  Any ideas how I can approach this? Thanks in advance! 🙂

  ---

  
  Twitter: @SQLife
  Email: sqlsalt(at)outlook(dot)com

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 8, 2011 at 1:44 pm

  #1365493

  DELETE f

  FROM (

  SELECT ROW_NUMBER() OVER (PARTITION BY FirstName, MiddleName, LastName ORDER BY DateLoaded) AS recID

  FROM dbo.Table1

  ) AS f

  WHERE recID > 1

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  August 9, 2011 at 12:41 am

  #1365635

  Thanks for the solution....