SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Delete duplicate rows keep just the latest dated
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

540 Posts

Posted - 02/24/2013 :  06:09:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3710 Posts

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

cplusplus
Aged Yak Warrior

540 Posts

Posted - 02/24/2013 :  08:37:22  Show Profile  Reply with Quote
Thank you very much James, it worked.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3710 Posts

Posted - 02/24/2013 :  10:17:30  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000