Re: SQL question: How to periodically read the last 'n' rows of a simple table?

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 02/03/05 

Date: Thu, 3 Feb 2005 00:39:27 -0600

There is nothing built in (thankfully, in my opinion)

> In this case, the data column's timestamp value is not the time of insert
> but rather a timestamp generated by a 3rd party application. There may be
> a significant gap between this timestamp and the time at insert.

I would just add a row_create_date with a type of datetime to your table,
with a default of getdate (assuming all of your current insert statements
name the columns that are being inserted into:

INSERT tablename (column1, column2, etc)
VALUES (...
OR
SELECT column1

And you will have your solution. Consider using GMT dates if you have to
deal with this sort of thing 24 hours a day, as daylight saving time can be
a pain once a year!) 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Hamelech Al Hakol" <djmarcusIGNORE@m5incIGNORE.com> wrote in message 
news:uwo5YEWCFHA.4052@TK2MSFTNGP15.phx.gbl...
> Hi
>
> I am painfully aware of the 'set' nature of the rows (order not defined), 
> however I was hoping that there was a vendor-specific way (as there is in 
> Oracle and I hear in SQL/Server 2005) to refer to a row number (in 
> acknowledgement that new data is generally appended to the file). Infact, 
> this is precisely how the app reads the data when its under Oracle - by 
> row number.
>
> The table in question actually does have a temporal-based column and I am 
> able to retrieve rows according to a time range. My application does read 
> the rows using a rolling time range (to keep up with the data).
>
> The issue is really a conceptual one: How to detect newly added data that 
> falls in an interval that has already been processed by the app?
>
> Once the app reads a range of time values it naturally moves the rolling 
> window (time range) to the next interval. So any data inserted in an 
> interval that has already been processed by the app is never detected !! 
> That is a glaring hole.
>
> In this case, the data column's timestamp value is not the time of insert 
> but rather a timestamp generated by a 3rd party application. There may be 
> a significant gap between this timestamp and the time at insert.
>
> Obviously given the ability to read a range of records based on row id, 
> makes the problem go away.
>
> -Thanks for the thought
> David
>
>
>
> Anith Sen wrote:
>> There is no such thing as the last 'n' rows in a table, since tables are 
>> logical representations of sets of entities. Sets by themselves has no 
>> inherent order and the tuples in a set are identified by the identifying 
>> attributes, not by positional numbers.
>>
>>
>>>>The table does not have a column with a uniquely increasing value. The 
>>>>database is read-only as far as my app is concenred, meaning I can't add 
>>>>a column, an index or otherwise make any changes to the table.
>>
>>
>> Unless you have a column which can determine the order in which the rows 
>> are inserted into the table, you cannot do this. Generally, in many such 
>> circumstances, the table designer might opt to include a column with 
>> temporal datatype ( in t-SQL, one might use DATETIME or SMALLDATETIME 
>> column ) that keeps track of the time of insertion. This would help in 
>> retrieving a specific subset that can be considered "last".
>>

Relevant Pages

  • Re: SQL question: How to periodically read the last n rows of a simple table?
    ... this is precisely how the app reads the data when its ... read the rows using a rolling time range. ... insert but rather a timestamp generated by a 3rd party application. ... > column) that keeps track of the time of insertion. ...
    (microsoft.public.sqlserver.programming)
  • Re: prevent multiple start of app
    ... Nils O. Selåsdal schrieb: ... The app is restarted by a wrapper-script if it terminates unexpected, so a race condition ist not very likely but not impossible if someone interferes with this by starting the app manually. ... I had the idea to write a allways updated timestamp into sharedmem and check if the timestam is older than 5 seconds but that's not a sure indication of the current state. ...
    (comp.os.linux.development.apps)
  • Re: Please advise on approach
    ... of the term timestamp - it does not refer to the sql data type in this ... rather to the datetime at which the source produced the data - ... I'm only interested in it differentiating between records ... > insertion order unless that order can be determined by the data itself. ...
    (microsoft.public.sqlserver.server)
  • Re: Time series deletion performance
    ... text value depending on customer need. ... Data is inserted into this table with timestamp ... partitioning), is there a better way to store or delete from this ... freed for insertion of data, I don't think from your description that ...
    (comp.databases.oracle.misc)
  • Re: How to get closing times of applications using shell
    ... the first line takes the timestamp and sends it to log file. ... line launches the app immediately and when i close the app the END ... i dont want the script to launch the app. ...
    (comp.unix.shell)
Loading