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.
| Author |
Topic |
|
takkone
Starting Member
8 Posts |
 Posted - 2011-05-19 : 10:18:04
|
| The scenario is we had a legacy quote database data which we exported old quotes out of, and imported them into our new SQL Server based quoting system. We imported every quote created for every customer since day 1 of our company's inception.We feel that having all these old quotes is like carrying around excess baggage, and would like to purge some of these quotes to trim down the tables a bit. I suggested a simple delete query, asking management for a cutoff date, so I could delete all quotes older than the date they give me. Management has a different idea. They want to keep up to 25 of the most recent quotes for each customer. What is the best way to accomplish this? To me it looks like some kind of "loop" would be required, but this is not my strong point. Let's simplify the situation and say the table I want to delete rows from is named "QUOTES" and the structure/data looks something like this:QuoteID | CustomerID | QuoteDate1 | 1 | 1/1/20012 | 2 | 2/3/20013 | 1 | 1/5/20014 | 1 | 3/1/2001etc...So QuoteID is the primary key, and the CustomerID is a foreign key linking back to the CUSTOMERS table (obviously a one-to-many relationship).I'd say I'm not a beginner at T-SQL and I have worked with cursors before. Just wondering what approach some expers would take to this challenge. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-19 : 10:25:00
|
| [code]WITH cte1 AS( SELECT *,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY QuoteId DESC) AS rn -- SELECT *,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY QuoteDate DESC) AS rn FROM YourTable)DELETE FROM cte1 WHERE rn > 25;[/code]If QuoteIds are not in chronological order, use the select statement that I have commented out, instead of the one that orders by QuoteId |
 |
|
|
takkone
Starting Member
8 Posts |
Posted - 2011-05-19 : 10:46:24
|
| Fantastic. Thank you!I'm going to have to do some reading about OVER and PARTITION BY. Very nice! |
|
|
|
|
|
|
Deleting Records based on Maximum Count