How To determine the order data is read from a MYSQL Database?

Question

is there a way to change the order data from a DB are read ? you know , the default order is that they are read from the first data inserted to the latest so is there a way to change it to latest->first?

Answer 1

Add a timestamp column to your table (or if your ID auto-increments, you could also use that) and do ORDER BY DESC.

The order things come in by default should be considered arbitrary. It's a good idea to always use ORDER BY when displaying data to users to avoid confusion.

Answer 2

You cannot change the default order-by. This behavior is not even specified by the server, it is specified by the Table Engine.

For example, no matter what order you insert into InnoDB, it will always default order on the PRIMARY KEY, which is different behavior than MyISAM

Answer 3

the default order is that they are read from the first data inserted to the latest

No, this is not correct. The default order depends on a lot of things and different execution plans can result in different orders. If you do not use an ORDER BY the order is indeterminate. This means you should not rely on it returning rows in insert order as this won't always be the case.

If you want to rely on the order you must add an order by clause. If you want to return the rows most recently inserted first then add a column insert_date, use the value NOW() when inserting and add this to your query:

ORDER BY `insert_date` DESC

As others have also pointed out, if you have an auto-incrementing primary key then you could order by that.

Answer 4

As others pointed out: 'ORDER BY' in your sql statement, though your datasets will need a timestamp field which is to be set with the current Date/time when new datasets are added.

You should then be able to order your data from first to last, or from last to first (ORDER BY myTimestampField DESC)

Answer 5

SELECT * FROM {tablename} ORDER BY id DESC

The above assumes you have an ID field that is incremented with each insert. You may also have a 'CreateDate' type field that you can use in the ORDER BY statement.

ORDER BY will default to an ascending order, so to get latest->first, use DESC.