Dear All,

Can anybody tell me that how can I access the last inserted record. Table is indexed.

Hoping for response,
Prabodh

__________________
Prabodh

What do you mean by "last"?

Remember that a table is a set and by definition a set is unordered; yet the concept of "last" implies an ordering of some form or another...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff,
'last' meant with the record inserted most recently from any terminal. Do I need to put any TIMESTAMP against each record or one more table that can keep track of the most recent record. I certainly don't want to do either of these two. Is there any in-built function that could help me?

You've defined 'last' to be the most recent row (not record, BTW) inserted into a table. In order to determine the relative recency of any given row implies that the rows are ordered by some time attribute; that is, there is some value in the row which implicitly or explicitly orders the rows in time. Remember that rows in a table are inherently unordered, so the 'last' row inserted into a table could in fact reside anywhere at all in the table, relative to the other rows. You need something to tell you the relative or absolute time that the row was inserted. The system does not do this for you.

Thus, to find the most recently inserted row means you must define some attribute of that row which orders it in time with respect to the other rows. One way to do this is to define an attribute of the row (i.e. a column) to contain a datetime data type. You can define a DEFAULT constraint on the column of current_timestamp (or GetDate() - they're equivalent). INSERTs will then automatically assign the current time to this column whenever the row is inserted; then finding the row with a maximum value of that column is straightforward.

Another way is to assign a monotonically increasing distinct value to some column every time a row is inserted. You could assign this value by manually incrementing a counter, or by assigning the identity attribute to the column. Again, to find the 'latest' entry is to simply find the maximum value of this column.

I suppose you could also create an auxiliary table which pointed to the 'latest' row, but managing this table seems problematic to me. YMMV.

Note that in SQL Server the TIMESTAMP datatype is a misnomer. In fact, a column of this data type is just a binary value guaranteed to be unique within a database, and is updated whenever a row is updated (and not just inserted). It actually has nothing to do with time. As I understand it, the definition does not guarantee that values will in fact increase, only that they are unique. The lack of this guarantee makes it unsuitable as a temporal ordering column, IMO. OTOH, I have always observed this value to steadily increase, but if you want to depend on this behavior you do so at your own risk.

You say you don't want to add any ordering information to your table, but to do as you wish, you have no choice.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanx for a quick response Jeff