Get the second last row of a table in MySQL?

You need to use ORDER BY clause to get the second last row of a table in MySQL.

The syntax is as follows.

select *from yourTableName order by yourColumnName DESC LIMIT 1,1;

To understand the above syntax, let us create a table. The query to create a table is as follows.

mysql> create table secondLastDemo
   -> (
   -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(10)
   -> );
Query OK, 0 rows affected (0.52 sec)

Insert some records in the table using insert command.

The query is as follows.

mysql> insert into secondLastDemo(StudentName) values('Larry');
Query OK, 1 row affected (0.15 sec)
mysql> insert into secondLastDemo(StudentName) values('Carol');
Query OK, 1 row affected (0.09 sec)
mysql> insert into secondLastDemo(StudentName) values('Bob');
Query OK, 1 row affected (0.10 sec)
mysql> insert into secondLastDemo(StudentName) values('Sam');
Query OK, 1 row affected (0.09 sec)
mysql> insert into secondLastDemo(StudentName) values('Mike');
Query OK, 1 row affected (0.10 sec)
mysql> insert into secondLastDemo(StudentName) values('David');
Query OK, 1 row affected (0.08 sec)
mysql> insert into secondLastDemo(StudentName) values('Maxwell');
Query OK, 1 row affected (0.10 sec)
mysql> insert into secondLastDemo(StudentName) values('Robert');
Query OK, 1 row affected (0.13 sec)
mysql> insert into secondLastDemo(StudentName) values('James');
Query OK, 1 row affected (0.14 sec)
mysql> insert into secondLastDemo(StudentName) values('Chris');
Query OK, 1 row affected (0.11 sec)
mysql> insert into secondLastDemo(StudentName) values('Ramit');
Query OK, 1 row affected (0.08 sec)

Display all records from the table using select statement.

The query is as follows.

mysql> select *from secondLastDemo;

The following is the output.

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1         | Larry       |
| 2         | Carol       |
| 3         | Bob         |
| 4         | Sam         |
| 5         | Mike        |
| 6         | David       |
| 7         | Maxwell     |
| 8         | Robert      |
| 9         | James       |
| 10        | Chris       |
| 11        | Ramit       |
+-----------+-------------+
11 rows in set (0.00 sec)

Here is the query to get the second last row of a table in MySQL.

mysql> select *from secondLastDemo order by StudentId DESC LIMIT 1,1;

The output displays the second last record.

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 10        | Chris       |
 +-----------+-------------+
1 row in set (0.00 sec)

Chandu yadav

Published on 29-Mar-2019 10:55:55

Previous Page Print Page