Is there any way to fetch the last N rows from a MySQL table without using auto-increment field or timestamp?

Question

There are many solutions in stackoverflow itself where the objective was to read the last n rows of the table using either an auto-increment field or timestamp: for example, the following query fetches the last ten records from a table named tab in the descending order of the field values named id which is an auto increment field in the table:

Select * from tab order by id desc limit 10

My question is: Is there any alternative way without having to get an auto increment field or timestamp to accomplish the task to get the same output?

Tips: The motivation to ask this question comes from the fact that: as we store records into tables and when query the database with a simple query without specifying any criteria like :

Select * from tab

Then the order of the output is same as the order of the records as inserted into the table. So is there any way to get the records in the reverse order of what they were entered into the database?

Answer 1

Data in mysql is not ordered- you don't have any guarantee on the order of the records you'll get unless you'll specify order by in your query.

So no, unless you'll order by timestamp, id, or any other field, you can't get the last rows, simply because there's no 'last' without the order

Answer 2

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

So if you don't have the data sorted by some id or some column then you cannot track the data based on its sorting. So it is not guaranteed how MYSQL will store the data and hence you cannot get the last n records.

You can also check this article:

Caveats

Ordering of Rows

In the absence of ORDER BY, records may be returned in a different order than the previous MEMORY implementation.

This is not a bug. Any application relying on a specific order without an ORDER BY clause may deliver unexpected results. A specific order without ORDER BY is a side effect of a storage engine and query optimizer implementation which may and will change between minor MySQL releases.