ใช้ subquery กับ LIMIT 2 แล้ว ORDER BY ... LIMIT 1 ไวยากรณ์:
SELECT * FROM (SELECT * FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 2) anyAliasName ORDER BY yourIdColumnName LIMIT 1;
สร้างตาราง:
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)
แทรกข้อมูล 10 แถว...
แสดงข้อมูล:
mysql> select * from lastRecordBeforeLastOne;
ผลลัพธ์:
+----+---------+------+ | 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)
คำสั่งดึงแถวที่สองจากท้าย (Id=9 Maxwell):
mysql> SELECT * FROM (SELECT * FROM lastRecordBeforeLastOne ORDER BY Id DESC LIMIT 2) tbl1 ORDER BY Id LIMIT 1;
ผลลัพธ์
+----+---------+------+ | Id | Name | Age | +----+---------+------+ | 9 | Maxwell | 29 | +----+---------+------+ 1 row in set (0.00 sec)
