CODEWITHSUNDEEP
sqlduplicates
Hossein Nassaj
Hossein Nassaj

Reputation: 3

how to remove a duplicate row in SQL with date condition?

my data is:

ID  Name    date
1   Ben 2017-01-21
2   Mark    2017-01-20
3   Mark    2017-01-21
4   Ell 2017-01-19

and it should be

ID  Name    date
1   Ben 2017-01-21
3   Mark    2017-01-21
4   Ell 2017-01-19

just the older "mark" with ID 2 must be remove

Upvotes: 0

Views: 12706

Answers (3)

Raishul
Raishul

Reputation: 146

It looks like the table has not been normalized. But as per the question following should work if the database is SQL Server 2008 and above, credit:

WITH cte AS (
      SELECT Id, Name, [Date]
         row_number() OVER(PARTITION BY Name ORDER BY [date]) AS [RowNum]
      FROM YourTable
    )
DELETE cte WHERE [RowNum] > 1

Upvotes: 0

K D
K D

Reputation: 5989

you can use following query. i usually avoid to add sub queries in select or where to avoid performance issues.

Select id, name date from mydata x
inner join (SELECT name,MAX(date) from mydata group by name) y on x.name=y.name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you just want to return the most recent row for name, you can use:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.name = t.name);

In most databases, you can use similar logic for a delete:

delete from t
    where t.date < (select max(t2.date) from t t2 where t2.name = t.name)

Upvotes: 7

Related Questions