CODEWITHSUNDEEP
sql
It Engineer
It Engineer

Reputation: 43

I want to get the second last row in a table

I want to get the second last row in table. By this query I get the last row but I want ot get the second last one with the value before max(id). Can anyone help me? Thanks in advance

query= "SELECT * from table WHERE id=(select max(id) from table)"

enter image description here

Upvotes: 0

Views: 1147

Answers (3)

Alva Santi
Alva Santi

Reputation: 83

You need to use ORDER BY clause to get the second last row of a table:

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 the 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;****

**+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 10        | Chris       |
 +-----------+-------------+

1 row in set (0.00 sec)** The output displays the second last record.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Assuming that rows are ordered by id, so the last row is the one with the largest id, then in Standard SQL, you would use:

select t.*
from t
order by t.id desc
offset 1 row fetch first 1 row only;

In databases that don't support offset/fetch there are usually similar methods.

Upvotes: 1

Marko Ivkovic
Marko Ivkovic

Reputation: 1290

Depend on your RDBMS...

Use LIMIT and OFFSET to skip last row

SELECT * 
FROM table
WHERE id = (
    SELECT MAX(id)
    FROM table
    LIMIT 1
    OFFSET 1
)

or you can use TOP

SELECT TOP 1 * 
FROM (
    SELECT TOP 2 * 
    FROM table ORDER BY id DESC) x                     
ORDER BY id

Upvotes: 2

Related Questions