keep the 100 most recent records

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)

keep the 100 most recent records

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-30 : 05:45:34

Hi I have a table where I'd like to keep the 100 most recent records, I've tried

DELETE FROM TableName 
ORDER BY RecordDate DESC 
LIMIT 100

which doesn't work, can someone please help me with this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-30 : 05:48:33

delete dt
(select row_number() over (order by RecordDate DESC) as rownum,* from TableName) as dt
where rownum > 100

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

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-30 : 06:48:33

webfred i'm gettign two syntax errors with this piece of code:

DELETE DT
(SELECT ROW_NUMBER() OVER(ORDER BY RecordID DESC) AS row_number, * 
FROM dbo.Table) AS DT
WHERE row_number > 100

1)Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
2)Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-30 : 07:00:09

Try:

;With CTE
(SELECT ROW_NUMBER() OVER(ORDER BY RecordID DESC) AS row_num, *
FROM dbo.Table)

Delete from CTE where Row_num > 100

I have not carried out testing.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-30 : 07:17:47

quote:
Originally posted by eljapo4

webfred i'm gettign two syntax errors with this piece of code:
DELETE DT
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY RecordID DESC) AS row_number, *
FROM dbo.Table) AS DT
WHERE row_number > 100

1)Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
2)Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.