CODEWITHSUNDEEP
sqlsql-serversql-delete
RustyHamster
RustyHamster

Reputation: 359

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

Upvotes: 1

Views: 48

Answers (4)

K.K
K.K

Reputation: 366

This helps u, and max(projectid) helps to find the last verified. so the query become simple

DELETE FROM TABLE1 WHERE PROJECTID NOT IN 
(SELECT MAX(PROJECTID) FROM TABLE1 GROUP BY CLIENTID)

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

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

Upvotes: 1

Manish Singh
Manish Singh

Reputation: 1024

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

Upvotes: 0

jarlh
jarlh

Reputation: 44805

Delete a row if there exists another row with same ClientID that is newer:

delete from tablename t1
where exists (select * from tablename t2
              where t2.ClientID = t1.ClientID
                and t2.LastVerified > t1.LastVerified)

Upvotes: 0

Related Questions