RustyHamster RustyHamster - 2 months ago 7
SQL Question

Removing Duplicated Values Based on Lastupdated Date SQL

I am trying to remove duplicated cases on our database.
There are 3 fields:

ProjectID
,
ClientID
,
LastVerified
.

We had an issue with one of our apps and it has created multiple new
ProjectID
.

What i want to do is remove the duplicates and only leave the row that was last Verified.
For instance:

ProjectID ClientID LastVerified
20773 336106 2016-07-29 01:38:37.450
20869 336106 2016-08-23 11:19:51.153


Here i would like to keep the second row as it was last verified.

I am using SQL database
Any help would be appreciated
Thanks

Answer

You can do this:

DELETE X FROM
(
     SELECT *, ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY LastVerified DESC) R
     FROM @t
) X
WHERE R!=1