Questionable SQL practice - Order By id rather than creation time

Question

So I have an interesting question that I am not sure is considered a 'hack' or not. I looked through some questions but did not find a duplicate so here it is. Basically, I need to know if this is unreliable or considered bad practice.

I have a very simple table with a unique auto incrementing id and a created_at timestamp. (simplified version of my problem to clarify the concept in question)

+-----------+--------------------+
| id        |created_at          |
+-----------+--------------------+
| 1         |2012-12-11 20:35:19 |
| 2         |2012-12-12 20:35:19 |
| 3         |2012-12-13 20:35:19 |
| 4         |2012-12-14 20:35:19 |
+-----------+--------------------+

Both of these columns are added dynamically so it can be said that a new 'insert' will ALWAYS have a greater id and ALWAYS have a greater date.

OBJECTIVE - very simply grab the results ordered by created_at in descending order

SOLUTION ONE - A query that orders by date in descending order

SELECT * FROM tablename
ORDER BY created_at DESC

SOLUTION TWO - A query that orders by ID in descending order

SELECT * FROM tablename
ORDER BY id DESC

Is solution two considered bad practice? Or is solution two the proper way of doing things. Any explanation of your reasonings would be very helpful as I am trying to understand the concept, not just simply get an answer. Thanks in advance.

Answer 1

In typical practice you can almost always assume that an autoincrement id can be sorted to give you the records in creation order (either direction). However, you should note that this is not considered portable in terms of your data. You might move your data to another system where the keys are recreated, but the created_at data is the same.

There is actually a pretty good StackOverflow discussion of this issue.

The basic summary is the first solution, ordering by created_at, is considered best practice. Be sure, however, to properly index the created_at field to give the best performance.

Answer 2

You shouldn't rely on ID for anything other than that it uniquely identifies a row. It's an arbitrary number that only happens to correspond to the order in which the records were created.

Say you have this table

ID  creation_date
1   2010-10-25
2   2010-10-26
3   2012-03-05

In this case, sorting on ID instead of creation_date works.

Now in the future you realize, oh, whoops, you have to change the creation date of of record ID #2 to 2010-09-17. Your sorts using ID now report the records in the same order:

1   2010-10-25
2   2010-09-17
3   2012-03-05

even though with the new date they should be:

2   2010-09-17
1   2010-10-25
3   2012-03-05

Short version: Use data columns for the purpose that they were created. Don't rely on side effects of the data.