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

I want to access the 2nd maximum booking_id for user 1. The expected result is user_id = 1, booking_id = 14.

I've been working over these hellish flames for way too long, this doesn't do any good:

select booking.user_id, b1.booking_id from booking 
left join(select 
            user_id, 
            booking_id 
          from booking 

          where booking_id = (select 
                                max(booking_id) 
                              from booking 
                              where booking_id <> (select 
                                                     max(booking_id) 
                                                   from booking)) 
        group by user_id) 
as b1 on b1.user_id = booking.user_id
where booking.user_id = '1'

Please note I've managed to do it as a calculated column but that's useless, I need the derived table.

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