How to purge all but the top N rows from a table

Question

Post reply

How to purge all but the top N rows from a table

Rod at work

SSC-Dedicated

Points: 34008
More actions
February 1, 2012 at 9:40 am

#252145

I tried asking this question on AskSqlServerCentral, but I think its undergoing PM work now, so please bear with me.
I've got a simple table, with the following structure:
CREATE TABLE LastUpdates (
LastUpdateID int IDENTITY(1,1) NOT NULL,
DateLastUpdated datetime NOT NULL,
CONSTRAINT PK_LastUpdates PRIMARY KEY CLUSTERED
(
LastUpdateID ASC
)
) ON PRIMARY
What I want to be able to do is purge all but the latest 10 records or so, based upon the DateLastUpdated value. How do I go about doing that?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

Here's one way.

I can think of at least one other.

DELETE FROM LastUpdates

WHERE LastUpdateID NOT IN

(

SELECT TOP 10 LastUpdateID

FROM LastUpdates

ORDER BY DateLastUpdated DESC)

Jared SSC Guru Points: 61793 More actions · Answer 2

If it is a lot of data and you do not want the overhead of deletes, I would insert the 10 rows you want to keep into a temp table, truncate the existing, and then insert back in. Depends on how much data you have and if you need to delete these rows as output.

Jared
CE - Microsoft

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 3

Thank you, MysteryJimbo, that looks like it will work.

Kindest Regards, Rod Connect with me on LinkedIn.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 4

At this point I don't have any data at all in the table, SQLKnowItAll, so I can be flexible as to how I handle this.

Kindest Regards, Rod Connect with me on LinkedIn.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Rod at work (2/1/2012)
At this point I don't have any data at all in the table, SQLKnowItAll, so I can be flexible as to how I handle this.

In that case, don't do deletes. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)