February 6, 2012 at 12:53 pm
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
February 6, 2012 at 1:16 pm
Hi and welcome to SSC. It is customary to provide ddl (create table scripts), sample data (insert statements) and desired output based on your sample data when posting questions. I went ahead and created these for you. This way everybody is on the same page.
The easiest way I know of to accomplish is using ROW_NUMBER like this.
create table #Doc
(
DocNum varchar(10),
Version int
)
insert #Doc (DocNum, Version)
values ('doc1', 0),('doc1', 1),('doc2', 0),('doc2', 2),('doc2', 3),
('doc3', 0),('doc3', 4),('doc3', 5),('doc3', 6)
--select *
delete x
from
(
select ROW_NUMBER() over(PARTITION by DocNum order by Version desc) as RowNum, *
from #Doc
) x
where RowNum > 2
select * from #Doc
--edit: Added desc to the order by to keep the largest 2 instead of the smallest.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 1:51 pm
thanks a lot for the quick response and a great solution that looks very simple
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply