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

Question

There are many solutions in stackoverflow in which the goal was to read the last n rows of the table using either an auto-increment field or a timestamp: for example, the following query retrieves the last ten records from a table named tab in descending order of the values ​​of the field named id, which is the 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 the auto-zoom field or timestamp to complete the task in order to get the same result?

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

 Select * from tab 

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

Answer 1

In the SQL world, order is not an integral property of a dataset. Thus, you do not receive any guarantees from your RDBMS that your data will come back in a certain order - or even in a sequential order - unless you request your data with an ORDER BY clause.

So, if you do not have data sorted by some identifier or column, then you cannot track data based on its sorting. Thus, it is not guaranteed how MYSQL will store the data and, therefore, you will not be able to get the last n records.

You can also check this article:

Warning

Row order

In the absence of ORDER BY, records can be returned to a different implementation than the previous MEMORY implementation.

It's not a mistake. Any application using a specific order without an ORDER BY clause may produce unexpected results. A specific order without ORDER BY is a side effect of the storage and query optimizer mechanism, which can and will vary between minor MySQL releases.

Answer 2

The data in mysql is not ordered - you have no guarantee on the order of the records you will receive if you do not specify order by in your request.

So, no, unless you order by timestamp, id or any other field, you cannot get the last lines simply because there is no “last” without order

Answer 3

Consider the following ...

 CREATE TABLE fruits (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,fruit VARCHAR(12) NOT NULL UNIQUE); INSERT INTO fruits (fruit) VALUES ('apple','orange','banana','pear'); SELECT * FROM fruits ORDER BY id; +----+--------+ | id | fruit | +----+--------+ | 1 | apple | | 2 | orange | | 3 | banana | | 4 | pear | +----+--------+ SELECT * FROM fruits; +----+--------+ | id | fruit | +----+--------+ | 1 | apple | | 3 | banana | | 2 | orange | | 4 | pear | +----+--------+