| Author |
Topic |
|
jun0
Starting Member
32 Posts |
 Posted - 2013-06-12 : 04:34:45
|
| 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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 04:44:02
|
| -- You can query for oldest 1000 records by using any dateColumn/auto-incremented column in the tableSELECT TOP 1000 *, ROW_NUMBER() OVER(ORDER BY dateColumn ASC) RNFROM YourTableORDER BY RN-- Then delete looks likeDELETE FROM (SELECT TOP 1000 *, ROW_NUMBER() OVER(ORDER BY dateColumn ASC) RN FROM YourTable) TempWHERE RN <= 1000--Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 04:51:28
|
| oldest rows based on what? do you've an identity field? or an audit date field?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jun0
Starting Member
32 Posts |
Posted - 2013-06-12 : 05:08:26
|
| 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... |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 05:10:45
|
| Do you have columns related to datetime or auto-increment(IDENTITY) column?--Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 05:21:44
|
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 sensethere'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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jun0
Starting Member
32 Posts |
Posted - 2013-06-12 : 05:27:00
|
| 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... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 05:30:53
|
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 alwaysdont 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 05:35:32
|
what you could try is thisSELECT *FROM(SELECT ROW_NUMBER() OVER (ORDER BY CONVERT(bigint,timetsmpcol) ASC) AS Seq,*FROM table)tWHERE Seq <=1000 to get first 1000 rows based on your timestamp and then convert it to a delete once you're happyDELETE tFROM(SELECT ROW_NUMBER() OVER (ORDER BY CONVERT(bigint,timetsmpcol) ASC) AS Seq,*FROM table)tWHERE 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jun0
Starting Member
32 Posts |
Posted - 2013-06-12 : 05:37:42
|
| 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? |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-12 : 05:41:12
|
| one thing you do...Post some sample data and also expected result with explanation for your requirement--Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-12 : 05:42:11
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
Query to delete oldest 1000 rows?