Will lowest autonumber always be first added?

[mscertified]

In order to get the first row added to a table, can I just take the lowest
autonumber PK value or do I need a timestamp column?

If I need a timestamp, what is the most efficient query to get the PK value
for the lowest timestamp. Currently I am using:

SELECT ID
FROM tblPortal
WHERE Timestamp IN (SELECT MIN(Timestamp) FROM tblPortal);

I realize this can return multiple rows but I dont care as I just read the
first one.

 

[Douglas J. Steele]

In order to get the first row added to a table, can I just take the lowest
autonumber PK value or do I need a timestamp column?

I'd say you need the timestamp column. Just in case you ever replicate your
application, since replication changes the Autonumber field to Random, not
Sequential.

If I need a timestamp, what is the most efficient query to get the PK
value
for the lowest timestamp. Currently I am using:

SELECT ID
FROM tblPortal
WHERE Timestamp IN (SELECT MIN(Timestamp) FROM tblPortal);

I don't think you really have any other option!

 

[Marshall Barton]

In order to get the first row added to a table, can I just take the lowest
autonumber PK value or do I need a timestamp column?

If I need a timestamp, what is the most efficient query to get the PK value
for the lowest timestamp. Currently I am using:

SELECT ID
FROM tblPortal
WHERE Timestamp IN (SELECT MIN(Timestamp) FROM tblPortal);

I realize this can return multiple rows but I dont care as I just read the
first one.

AutoNumbers are not reliable for this purpose.

An alternative query could be:

SELECT TOP 1 *
FROM tblPortal
ORDER BY TimeStamp

Assuming the TimeStamp field is set using Now(), you should
not have many worries about user created records clashing.
If the records are created programatically in a loop, then
you could get dozens of records with the same TimeStamp
value. If multiple records with the same TimeStamp is an
issue and assuming that it doesn't matter a lot which one is
returned, change the sort to:
ORDER BY TimeStamp ASC, ID DESC

I am having trouble imagining a reason for all this.

 

[mscertified]

This is a queue table populated by a web page. The Access db reads in the
records on a FIFO basis, hence the need to know the first added.

 

[Marshall Barton]

This is a queue table populated by a web page. The Access db reads in the
records on a FIFO basis, hence the need to know the first added.

Then it isn't really critical how you resolve ties.

I made a mistake with the sort fo the query that returns a
single record. It should have been:

ORDER BY TimeStamp, ID