Reputation: 359
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
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
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
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
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