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
 General SQL Server Forums
 New to SQL Server Programming
 Sql compact server (4.0) row number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hapetter
Starting Member

10 Posts

Posted - 06/13/2013 :  07:19:32  Show Profile  Reply with Quote
Hi.

This is my application.

I add 120 rows of data when an event occurs (every sec) using Union ALL insert . Then I check my row cound by a select statement. Then I like to remove the oldest (120) rows if rowcount is above a limit (ex. 10000 rows). Hence it will work as a FIFO.

So my question is: Is it possible to use row index number for creating the delete rows query: something like: 'Select * From eTable Where "rowIndex < 120'

Can row index be used? Will row index update after deleting rows so that the oldest row always starts at row index 0?

Thanks



Regards
Hans

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/13/2013 :  07:35:01  Show Profile  Reply with Quote
Nope Rowindex doesnt automatically change upon delete
you need to use OFFSET..FETCH syntax as ROW_NUMBER is not available in SQL Server Compact

ie something like

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TempTable')
  DROP TABLE TempTable
SELECT * INTO TempTable
FROM YourTable
ORDER BY RowIndex
OFFSET 0 ROWS
FETCH FIRST 120 ROWS

DELETE t
FROM YourTable t
JOIN TempTable tmp
ON tmp.RowIndex = t.RowIndex



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

hapetter
Starting Member

10 Posts

Posted - 06/13/2013 :  08:05:26  Show Profile  Reply with Quote
In other words, how to delete the oldest 120 rows in a table without knowing/caring about the column content

Regards
Hans
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/14/2013 :  00:42:37  Show Profile  Reply with Quote
quote:
Originally posted by hapetter

In other words, how to delete the oldest 120 rows in a table without knowing/caring about the column content

Regards
Hans


See my suggestion above

you'll have to specify "oldest" in terms of one or more columns. There's no concept of old and new in sql table otherwise.
My given solutions finds it based on your RowIndex column values

------------------------------------------------------------------------------------------------------
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.06 seconds. Powered By: Snitz Forums 2000