How to delete all records but the top 40

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 2005 Forums

Transact-SQL (2005)

How to delete all records but the top 40

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-04-28 : 10:55:49

Can someone help me out with the following query request?
How can I delete all but the TOP 40 records from a table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-28 : 11:00:13

How do you decide which records are the top 40?

No, you're never too old to Yak'n'Roll if you're too young to die.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-04-28 : 11:01:26

Lets assume they were added to the db in the correct order.
So, I guess row number would be fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:08:30

quote:
Originally posted by qman

Lets assume they were added to the db in the correct order.
So, I guess row number would be fine.

so you do have a rownumber? unless you've such a column you cant guarantee the order.

in that case it will be like


DELETE t
FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY RowNoCol ASC) AS Seq
 FROM Table
)t
WHERE Seq>40

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-28 : 11:10:01

That is not the half of an answer.
Please give example data (4 rows) and then show us how you would select top 2 of them.
Maybe then it is more clear.

No, you're never too old to Yak'n'Roll if you're too young to die.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-04-28 : 11:13:06

Thanks guys....

visakh16, that's what I was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:14:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources