How to select last 10 rows from MySQL?

To select last 10 rows from MySQL, we can use a subquery with SELECT statement and Limit concept. The following is an example.

Creating a table.

mysql> create table Last10RecordsDemo
   -> (
   -> id int,
   -> name varchar(100)
   -> );
Query OK, 0 rows affected (0.75 sec)

Inserting records into the table.

mysql> insert into Last10RecordsDemo values(1,'John'),(2,'Carol'),(3,'Bob'),(4,'Sam'),(5,'David'),(6,'Taylor');
Query OK, 6 rows affected (0.12 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into Last10RecordsDemo values(7,'Sam'),(8,'Justin'),(9,'Ramit'),(10,'Smith'),(11,'Clark'),(12,'Johnson');
Query OK, 6 rows affected (0.14 sec)
Records: 6  Duplicates: 0  Warnings: 0

To display all records.

mysql> select *from Last10RecordsDemo;

The following is the output.

+------+---------+
| id   | name    |
+------+---------+
|    1 | John    |
|    2 | Carol   |
|    3 | Bob     |
|    4 | Sam     |
|    5 | David   |
|    6 | Taylor  |
|    7 | Sam     |
|    8 | Justin  |
|    9 | Ramit   |
|   10 | Smith   |
|   11 | Clark   |
|   12 | Johnson |
+------+---------+
12 rows in set (0.00 sec)

The following is the syntax to get the last 10 records from the table. Here, we have used LIMIT clause.

SELECT * FROM (
   SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10
)Var1
   ORDER BY id ASC;

Let us now implement the above query.

mysql> SELECT * FROM (
   ->     SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10
   -> )Var1
   ->
   -> ORDER BY id ASC;

The following is the output that displays the last 10 records.

+------+---------+
| id   | name    |
+------+---------+
|    3 | Bob     |
|    4 | Sam     |
|    5 | David   |
|    6 | Taylor  |
|    7 | Sam     |
|    8 | Justin  |
|    9 | Ramit   |
|   10 | Smith   |
|   11 | Clark   |
|   12 | Johnson |
+------+---------+
10 rows in set (0.00 sec)

We can match both records with the help of the SELECT statement.

Total Records (12)

Last 10 Records (10)

Chandu yadav

Updated on: 12-Sep-2023

30K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started