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)
 keep top 10 records from history

Author  Topic 

ArielR
Starting Member

21 Posts

Posted - 2015-03-02 : 14:02:51
For each change to items table a trigger store the old record values at a history table. I want to keep only the 10 last records of each item.
The history table has itemID as int, dDate as SmallDateTime, dOldPriceValue as decimel columns. I would delete records from history if the itemID exceeds 10 history records. Could you help me with the tsql query. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-02 : 14:21:22
Something like this?
DELETE	f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY OrderDate DESC) AS rn
FROM dbo.History
) AS f
WHERE rn >= 11;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ArielR
Starting Member

21 Posts

Posted - 2015-03-02 : 18:11:11
Haa!! thank you. That's beautiful. Works very well.
Go to Top of Page
   

- Advertisement -