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.

score 1 · Answer 1 · answered Feb 05 '21 at 14:52

1

A simple way to do it is using LIMIT OFFSET:

SELECT *
FROM booking
WHERE user_id = 1
ORDER BY booking_id DESC
LIMIT 1 OFFSET 1

Demo here

answered Feb 05 '21 at 14:52

Giorgos Betsos

71,379
9
63
98

we wrote same answer at same time :D – James Stevens Feb 05 '21 at 15:03
my answer is longer :) – James Stevens Feb 05 '21 at 15:32
Dear James and Giorgos, thank you so much for your replies. They first worked, but then they didn't. Here's a bit of background: Every day, I get a list of reservations that were canceled. I get this from booking table (select b.id, b.user_id from booking b where b.canceled = 1 and b.date_updated > datesub(now(), interval 1 day)). The users who cancel are the vendors, and apart from the reservation that was cancelled yesterday, I also need to return the previous reservation. This is why I'm using join: I already have the current booking(canceled yesterday), the user, and now – bella lugosi Feb 05 '21 at 15:56
I need to catch the one that happened immediately before the one that triggered me This technically means I need to use 2x the booking table - the first one that locates all yesterday's cancelled bookings, and the second one that returns the predecessors for the particular user. I cannot use where user_id = '1', I need to use join (booking) on user_id = user_id, but it messes me up big time as it constantly returns the current booking (the max one) – bella lugosi Feb 05 '21 at 15:56
If the question you asked is not the question you want solved, you need to ask the question you want solved. Really. Try starting again, this time asking the question you want answered as clearly as possible. – James Stevens Feb 05 '21 at 16:04
1

I'll go ahead and do that for sure. You guys definitely helped but there's a piece missing, I thought I'd be able to use your solution but I was wrong. Thanks anyway man, I learned something new today :) – bella lugosi Feb 05 '21 at 16:05
If you want the second booking for the user who holds the highest booking, then `SELECT * FROM booking WHERE user_id in (select user_id from booking order by booking desc limit 1) ORDER BY booking_id DESC LIMIT 1 OFFSET 1` ---- in this the sub-query finds the highest entry - I've changed my answer to reflect this. I'm still not 100% certain I understand what you are asking :D ... but you can do what you want in the sub-query to make sense for you :) – James Stevens Feb 06 '21 at 17:17

James Stevens · Accepted Answer · 2021-02-06T17:20:38.003

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

score 0 · Answer 3 · answered Feb 05 '21 at 14:48

By using the answer in this question What is the simplest SQL Query to find the second largest value? https://stackoverflow.com/a/7362165/14491685

you can integrate with your query to get it like this:

select * from booking 
where booking_id =
(select max(booking_id) from booking
where user_id =1 
and booking_id not in (SELECT MAX(booking_id ) FROM booking  ))

Get the next max(id)

3 Answers3