1

I'm intrigued about sorting results in a select statement by an autonumber that's the primary key in the table.

Ultimately I'm looking to select the most recent record from the table and notice that the execution time is significantly more when using ORDER BY DateTime DESC than using ORDER BY Id DESC.

Is it bad practice though to assume that the highest numbered autonumber / primary key would be the last record?

Thanks Gareth

Gareth
  • 5,140
  • 5
  • 42
  • 73

1 Answers1

3

Is it bad practice though to assume that the highest numbered autonumber / primary key would be the last record?

If you mean the last inserted record then that is a decent approach - autonumber will never go "backwards" (but can go negative as mentioned below).

However, there's a few edge cases that you don't specify which may change the right answer:

  • what if two records have the same DateTime value? Would they both be considered the "last" record?
  • Would you ever insert a record with an earlier DateTime (say as an import from an external source or merging two datasets.
  • Will you have more than 2 Billion records? If so then you rick having the autoincrement integer rolling over to a negative number

EDIT

Based on comments and donwvotes, I'm revising my answer to agree that an autoincrement field is not the best solution to determine the "last" record. There are too many cases where it could be wrong to recommend it.

All in all, if your definition of "last" is "the record with the highest DateTime value" - then use that!

You can add an index on DateTime which will speed up queries against that column significantly.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • 1
    `autonumber will never go "backwards".` Yes, it will. It can be negative. You can never depend on an autonumber to be anything except unique, and not even that, if you mess about too much. – Fionnuala Mar 19 '14 at 13:00
  • Thanks for the answer / comments. The `DateTime` could not be the same as part of the select statement has a condition which specifies `userid` and one user could not enter two records simultaneously. There is a potential for datasets to be merged which I hadn't thought of thanks. I've tried indexing the `DateTime` but it still adds a good amount of time on to the query. – Gareth Mar 19 '14 at 13:23