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
 General SQL Server Forums
 New to SQL Server Programming
 Sql compact server (4.0) row number

Author  Topic 

hapetter
Starting Member

10 Posts

Posted - 2013-06-13 : 07:19:32
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

52326 Posts

Posted - 2013-06-13 : 07:35:01
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 - 2013-06-13 : 08:05:26
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

52326 Posts

Posted - 2013-06-14 : 00:42:37
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
   

- Advertisement -