Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Delete duplicate rows keep just the latest dated

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-24 : 06:09:50
I would like to Delete duplicate rows, keeping just the latest based on progid & Update_date.

Delete from tab_prog based on progid/Update_date columns, keeping just latest update_date row.

When i run the below query i can see lot of progid's which has duplicate rows.

select progid from tab_prog group by progid having count(*) >1


Thanks a lot for the helpful info.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-24 : 08:21:12
[code]DELETE t FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY progid ORDER BY update_date DESC) AS RN
FROM tab_prog
) t WHERE RN > 1;[/code]
Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-02-24 : 08:37:22
Thank you very much James, it worked.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-24 : 10:17:30
You are very welcome - glad to help.
Go to Top of Page
   

- Advertisement -