13

How can I order values from a table, ascending from the time they were inserted. There is no special column for this matter, like a timestamp or autoincrement.

I know this is not recommended to do... Still I would like to know how to do this.

As I understand from the answers, if no sorting columns e.g: timestamp or autoincremental were added before the values were inserted, there is no way of sorting them by insertion.

jacktrades
  • 7,224
  • 13
  • 56
  • 83

4 Answers4

13

There is no guarantee that rows will be returned in any particular order without an ORDER BY clause in the query.

Consider a simple query that returns all columns of all rows in a table. For example:

SELECT * FROM mytable ;

For that query, it is likely that MySQL will perform a full table scan, from the beginning of the table. So it is likely that the rows will be returned in the order they are found in physical storage.

This may roughly correspond to the order that rows were inserted, if there have been no deletes, no updates and no reorganization, where space for an inserted row was later reclaimed, and reused to store a newly inserted row.

But this behavior is NOT guaranteed.

To return the rows in the order that they were inserted, the query must specify the sequence that rows are to be returned, by including an ORDER BY clause.

For the rows to be returned in "insertion order", that means the query needs to be able to have that information available, or be able to derive that. For a simple query against a single table, that means the information needs to be stored in the row.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Good answer. Yes this behavior is not guaranteed, I can tell because they are not correspondingly ordered by the time of insertion. – jacktrades Jul 25 '12 at 18:59
  • But what about sorting rows in descending order as they are inserted – Bhavesh G Sep 02 '14 at 19:08
  • 1
    @Bhavesh Gangani: Yes, rows could be inserted in a particular sequence. But that does *not* guarantee that a SELECT statement will return rows in the order the rows were inserted, or in any particular sequence, except the sequence specified in an `ORDER BY` clause. If we need to return rows in the order that they were inserted, we'd need to have that information available for each row. That's usually stored in the row itself, but in the edge case, it could be stored in another table where it can be derived for each row. – spencer7593 Sep 02 '14 at 20:43
  • I'm generating unique id for a row at application level as primary key(column name : `prod_id`), I don't having any `date` coumn in table. so just `ORDER BY prod_id DESC`) works, its returns descending order as inserted .. Is is reliable .. ? – Bhavesh G Sep 02 '14 at 20:46
  • @Bhavesh: In that case, you actually have an auto_increment `prod_id` column. (OP did not have an auto_increment column.) The `ORDER BY` clause you your query is guaranteed to return the rows in the specified sequence. That is reliable, yes. (Rows that have `prod_id` automatically assigned will have "higher" values than previously inserted rows. But the database would *not* (necessarily) prevent the value of `prod_id` on a row from being modified by an UPDATE statement. Nor would an INSERT statement be prevented from supplying a value for `prod_id` that is lower than the highest value.) – spencer7593 Sep 02 '14 at 20:53
  • @spencer7593 No !! I'm generating primary key values at application level (ex. php side). I d'nt have any `autoincrement` column – Bhavesh G Sep 02 '14 at 20:54
  • @Bhavesh Gangani: I apologize. I misread your comment. Yes, you can rely on the database returning rows in the sequence specified in an ORDER BY clause. The database itself doesn't guarantee that `prod_id` value of a previously inserted row always having a lower value than a subsequently inserted row. That would depend on the application mechanism that generates `prod_id` values (guaranteeing that generated `prod_id` values are "higher" than previously generated values), and on the application inserting rows in the same order that `prod_id` values were generated. – spencer7593 Sep 02 '14 at 21:02
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/60477/discussion-between-bhavesh-gangani-and-spencer7593). – Bhavesh G Sep 02 '14 at 21:06
1

You can ORDER BY something you can get out of your table. If you do not have anything in there that can be used to find out the order you need, you cannot order by it.

Nanne
  • 64,065
  • 16
  • 119
  • 163
  • 2
    Well, it would be weird to add an answer that I know to be false, now, wouldn't it? The answer you accepted basically says the same, but somehow seems to imply you could do something else, that you really can't. If you can't order, you can't order. The rest isn't really worth mentioning.. – Nanne Jul 25 '12 at 19:17
1

Depending on the data in the table, you may be able to order by the id of the data - if the data has a single incremental integer to assure PK uniqueness. There is no other way to sort on insertion order unless the data is captured and recorded in the table.

I don't know of anything in MySQL that retains extra (meta) information on records that you have not specified at the table level.

Noah
  • 1,966
  • 1
  • 14
  • 29
0

There needs to be a column to order your query by. Usually this would be an insertion timestamp, or incrementing id/incrementing key. There is no way to guarantee the order otherwise, because there is no record of it.

relevant thread from MySQL forum

Dima
  • 23,484
  • 6
  • 56
  • 83
  • 100% sure that MySQL has no record about the time of insertion or anything similar to sort this? – jacktrades Jul 25 '12 at 18:48
  • Why not just make add a timestamp column? And yes, 100% sure. To order a query, you need something to order it *by*. – Dima Jul 25 '12 at 18:50
  • Read that from MySQL forum: `Generally, you should not rely on the order of rows returned by the DB when you have not specified any order` I see no certainty. – jacktrades Jul 25 '12 at 18:50
  • The only way to get what you want for those rows is to give them ids or timestamps too (only possibly if you already know how they should be ordered, though). – Dima Jul 25 '12 at 18:52
  • The quote you posted is probably referring to the fact that the query will generally be returned sorted somehow (like if it is just string values, it will be in alphabetical order), but without specifying a column, you can't be sure. The problem here is with the way you are storing your data, not with MySQL. – Dima Jul 25 '12 at 18:55