SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query to delete oldest 1000 rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jun0
Starting Member

United Kingdom
32 Posts

Posted - 06/12/2013 :  04:34:45  Show Profile  Reply with Quote
Hi,

I have data being inserted into a table periodically and i also want to set up a truncate job to run periodically to prune the oldest rows in the table.

What query would I use to check for the oldest 1000 rows in the table for example, and then delete those rows?

Sorry but still new and not quite sure how to go about this.

thankyou all

Edited by - jun0 on 06/12/2013 04:35:13

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/12/2013 :  04:44:02  Show Profile  Reply with Quote
-- You can query for oldest 1000 records by using any dateColumn/auto-incremented column in the table
SELECT TOP 1000 *, ROW_NUMBER() OVER(ORDER BY dateColumn ASC) RN
FROM YourTable
ORDER BY RN

-- Then delete looks like
DELETE FROM (SELECT TOP 1000 *, ROW_NUMBER() OVER(ORDER BY dateColumn ASC) RN FROM YourTable) Temp
WHERE RN <= 1000



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  04:51:28  Show Profile  Reply with Quote
oldest rows based on what? do you've an identity field? or an audit date field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 06/12/2013 :  05:08:26  Show Profile  Reply with Quote
This is the thing, I don't know what it can be based on. Its just the oldest rows that are in that table at the time, if there was such a function as 'BOTTOM' as opposed to top, then that would help greatly, is there such a function?

I just want to delete the bottom 1000 rows from the table say...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/12/2013 :  05:10:45  Show Profile  Reply with Quote
Do you have columns related to datetime or auto-increment(IDENTITY) column?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  05:21:44  Show Profile  Reply with Quote
quote:
Originally posted by jun0

This is the thing, I don't know what it can be based on. Its just the oldest rows that are in that table at the time, if there was such a function as 'BOTTOM' as opposed to top, then that would help greatly, is there such a function?

I just want to delete the bottom 1000 rows from the table say...


That doesnt make any sense
there's no concept of old or new, first or last in sql table unless you specify it by means of a columns or group of columns as indicated by the ORDER BY clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 06/12/2013 :  05:27:00  Show Profile  Reply with Quote
ok thankyou visakh16, i thought when data was loaded into the database, when you selected * from the table, the top column that you can see in the table was the last one loaded into the db (thats probably pretty embarrassing that I tought that then haha)

Anyway, there is a timestamp column but the data is not human readable...

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  05:30:53  Show Profile  Reply with Quote
quote:
Originally posted by jun0

ok thankyou visakh16, i thought when data was loaded into the database, when you selected * from the table, the top column that you can see in the table was the last one loaded into the db (thats probably pretty embarrassing that I tought that then haha)

Anyway, there is a timestamp column but the data is not human readable...




nope...the way you see result select * from table depends on lots of factors like presense of clustered index etc so you can guarantee it always

dont you've a datetime column atleast like created date? timestamp datatype is just an incrementing binary value which is used for row versioning. It has no relationship to date or time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  05:35:32  Show Profile  Reply with Quote
what you could try is this


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY CONVERT(bigint,timetsmpcol) ASC) AS Seq,*
FROM table
)t
WHERE Seq <=1000


to get first 1000 rows based on your timestamp and then convert it to a delete once you're happy


DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY CONVERT(bigint,timetsmpcol) ASC) AS Seq,*
FROM table
)t
WHERE Seq <=1000



but again keep in mind that the above solution is not guaranteed to delete the firstly inserted 1000 rows to the table as timestamp value will change automatically upon each update operation


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jun0
Starting Member

United Kingdom
32 Posts

Posted - 06/12/2013 :  05:37:42  Show Profile  Reply with Quote
No I don't :(

There aren't any indexes set up, If i did know that the top column was the last one inserted, is there a way to go about what I was talking about with deleting the 'bottom' rows?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/12/2013 :  05:41:12  Show Profile  Reply with Quote
one thing you do...
Post some sample data and also expected result with explanation for your requirement


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/12/2013 :  05:42:11  Show Profile  Reply with Quote
quote:
Originally posted by jun0

No I don't :(

There aren't any indexes set up, If i did know that the top column was the last one inserted, is there a way to go about what I was talking about with deleting the 'bottom' rows?



Thats what my last suggestion does based on the "current" bottom 1000 rows ie record age determined based on last modification time.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000