mysql - Get the next max(id)

Question

I've got the following table:

booking_id	user_id
11	1
12	76
13	932
14	1
15	626
16	1
17	3232

question from:https://stackoverflow.com/questions/66065253/get-the-next-maxid

Answer 1

If you are using MySQL, you can avoid the (rather messy) double sub-query by using LIMIT & OFFSET

Just add order by booking_id desc LIMIT 1 OFFSET 1 and you will get the second highest booking_id. For example ...

select * from booking where user_id = 1 order by booking_id desc OFFSET 1 LIMIT 1

I tested this on one of my tables & it worked fine. If you have an index on booking_id it should be really fast.

If you want the second highest booking for the user who holds the highest booking, then this should work

SELECT * FROM booking 
WHERE user_id in
   (select user_id from booking order by booking_id desc limit 1) 
ORDER BY booking_id DESC LIMIT 1 OFFSET 1 

The sub-query finds the user_id of the user with the highest booking, then the main query finds their second highest booking