How to get the second last record from a table in MySQL?

MySQLMySQLi Database

To get the record before the last one i.e. the second last record in MySQL, you need to use subquery.

The syntax is as follows

SELECT *FROM
(SELECT *FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 2)
anyAliasName
ORDER BY yourIdColumnName LIMIT 1;

Let us first create a table. The query to create a table is as follows

mysql> create table lastRecordBeforeLastOne
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > Name varchar(20) DEFAULT 'John',
   - > Age int DEFAULT 18
   - > );
Query OK, 0 rows affected (0.79 sec)

Now you can insert some records in the table using insert command.

The query is as follows

mysql> insert into lastRecordBeforeLastOne values();
Query OK, 1 row affected (0.21 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Larry',23);
Query OK, 1 row affected (0.19 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Mike',19);
Query OK, 1 row affected (0.20 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Sam',24);
Query OK, 1 row affected (0.22 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Bob',26);
Query OK, 1 row affected (0.13 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('David',22);
Query OK, 1 row affected (0.23 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('James',29);
Query OK, 1 row affected (0.14 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Carol',21);
Query OK, 1 row affected (0.23 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Maxwell',29);
Query OK, 1 row affected (0.18 sec)
mysql> insert into lastRecordBeforeLastOne(Name,Age) values('Chris',25);
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from lastRecordBeforeLastOne;

The following is the output

+----+---------+------+
| Id | Name    | Age  |
+----+---------+------+
|  1 | John    |   18 |
|  2 | Larry   |   23 |
|  3 | Mike    |   19 |
|  4 | Sam     |   24 |
|  5 | Bob     |   26 |
|  6 | David   |   22 |
|  7 | James   |   29 |
|  8 | Carol   |   21 |
|  9 | Maxwell |   29 |
| 10 | Chris   |   25 |
+----+---------+------+
10 rows in set (0.00 sec)

Here is the query to get the second last record in MySQL

mysql> SELECT *FROM
   - > (SELECT *FROM lastRecordBeforeLastOne ORDER BY Id DESC LIMIT 2) tbl1
   - > ORDER BY Id LIMIT 1;

The following is the output

+----+---------+------+
| Id | Name    | Age  |
+----+---------+------+
|  9 | Maxwell |   29 |
+----+---------+------+
1 row in set (0.00 sec)
raja
Published on 19-Mar-2019 10:18:36
Advertisements