Delete all versions except Max 2 versions

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

General SQL Server Forums

New to SQL Server Programming

Delete all versions except Max 2 versions

Author

Topic

Annee
Starting Member

1 Post

Posted - 2012-02-06 : 14:42:33

Need help writing a stored procedure that deletes all the records of a given doc-id except the top 2 max versions of that doc-id.
Example:

Doc-Id Version
------- -------
doc1 0
doc1 1

doc2 0
doc2 2
doc2 3

doc3 0
doc3 4
doc3 5
doc3 6

so in the above table
1)for doc1: Should not delete anything since only 2 versions exist
2)for doc2: Should delete record with version : 0 leaving behind
the 2 max versions 2 and 3
3)for doc3: Should delete records with versions : 0 and 4 leaving
behind the 2 max versions 5 and 6

Note: The version value for some trans can go as high as 50 or even more

Appreciate any help in developing most optimal query to perform the specified task

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-06 : 14:47:18

You can use the row-number function to do this - see below. Before you run it, comment the last line, uncomment the line before that and run the query to see the rows that will be deleted. Once you are comfortable, revert the comments and run.

;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY [doc-id] ORDER BY [version] DESC) AS RN
	FROM
		YourTable
)
-- SELECT * FROM cte WHERE RN > 2;   - uncomment and run this to see what will be deleted.
DELETE FROM cte WHERE RN > 2;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources