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

• Related Questions & Answers
• Get last second of a date in MySQL?
• How to get the second last record from a table in MySQL?
• Get total in the last row of MySQL result?
• Get the average row length of a MySQL table
• Get Last Entry in a MySQL table?
• Display sum in last row of table using MySQL?
• Get second largest marks from a MySQL table using subquery?
• How to get the second-to-last element of a list in Python?
• How to get the first and last record of the table in MySQL?
• Get the sum of multiple row (not all) values from a MySQL table?
• Get the last record from a table in MySQL database with Java?
• Access last inserted row in MySQL?
• MySQL query to get the last created table name (most recent)?
• Get the Average of Average in a single MySQL row?
• Get the number of columns in a MySQL table?