select max id minus one ( second last row id )

Question

Just wondering if there is a simple query that I could use to get the second last record when selecting max id.

SELECT MAX(`EventID`) FROM `event`;

Thank you

What do you want to do? Just get the last record in the database? — Naftali, Jul 08 '13 at 19:44
possible duplicate of [Select last row in MySQL](http://stackoverflow.com/questions/4073923/select-last-row-in-mysql) — Naftali, Jul 08 '13 at 19:44

score 13 · Answer 1 · answered Jul 08 '13 at 19:50

13

You can use: EDIT: (added Bill's suggestion which is better)

SELECT (`EventID`) FROM 'event' ORDER BY 'EventID' DESC LIMIT 1 OFFSET 1

OR:

SELECT `EventID` FROM (SELECT (`EventID`) FROM 'event' ORDER BY 'EventID' DESC LIMIT 2) ORDER BY 'EventID' ASC LIMIT 1

This solution is more general and will also work if your EventID column has gaps

answered Jul 08 '13 at 19:50

Ronen Yacobi

2

+1 but you can do this without a subquery by using LIMIT with OFFSET. – Bill Karwin Jul 08 '13 at 19:51
the one with offset is a nice idea :) the second one could return the last eventID -and not the last second- if there's only one row in the table (because LIMIT 2 will return just one row in that case) – fthiella Jul 08 '13 at 20:07

score 6 · Answer 2 · answered Jul 08 '13 at 19:50

6

SELECT MAX(EventID)
FROM   event
WHERE  EventID<(SELECT MAX(EventID) FROM event)

answered Jul 08 '13 at 19:50

fthiella

score 0 · Answer 3 · answered Jan 05 '22 at 11:50

select max(billno) from bill_information

solution

select max(billno) from bill_information where billno <(select max(billno) from bill_information)

This will help if you making billing software.That the billno (primary key with indexed) deleted and you want the billno between the billno

--213763 billno
select max(billno) from bill_information where billno <213763

score -2 · Answer 4 · answered Jul 17 '14 at 12:44

-2

i did something similar to this

$query_anakuklwsh = "SELECT * FROM anakuklwsh WHERE id=".$row_anakuklwsh['id']." -1";

answered Jul 17 '14 at 12:44

Dimitris Maniatis

score -3 · Answer 5 · answered Jul 08 '13 at 19:46

-3

I actually managed to figure it out

  SELECT MAX(`EventID`) FROM `event` WHERE `EventID` = (SELECT MAX(`EventID`) -1 FROM `event`);

answered Jul 08 '13 at 19:46

Deano

2

Until you come to a sequence with `4, 5, 6`, but `5` was deleted. – Dark Falcon Jul 08 '13 at 19:47

5 Answers5