How to select last 10 rows from MySQL?

MySQLMySQLi Database

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)

12 Records

Last 10 Records (10)

10 Records
raja
Published on 23-Nov-2018 10:26:08
Advertisements