Taxi Driver Queue management MySQL query

Question

I'm working on a Taxi Booking app back-end. I have a bookings table where I keep all the bookings information.

The main two fields in booking tables are:

driver_id

booking_time

For queue management, the business logic is first come first serve, and when queue is complete the process keeps on running in circular order.

For an example:

`id`  `driver_id` `booking_time`
 1     2           2015-06-24 10:00:12
 2     1           2015-06-24 10:05:25
 3     3           2015-06-24 10:10:36
 4     2           2015-06-24 10:30:41
 5     1           2015-06-24 10:45:45
 6     3           2015-06-24 10:47:18
 7     2           2015-06-24 10:50:49
 8     1           2015-06-24 10:55:49

According to the above business logic, the next booking should be offered to driver_id = 3 based on last booking time. Cos if we look at the very last 3 bookings, driver_id = 3 took the booking at 10:47:18, the other 2 drivers (driver_id = 1, 2) got bookings after 10:47. So driver 3 deserves the next booking.

Here is the SQL Fiddle: http://sqlfiddle.com/#!9/aa08b/6

SELECT tmp.driver_id, 
       tmp.booking_time 
FROM  (SELECT driver_id, 
              booking_time 
       FROM   bookings 
       -- GROUP BY driver_id 
       ORDER  BY booking_time DESC) tmp 
GROUP  BY tmp.driver_id 
ORDER  BY tmp.booking_time ASC 
LIMIT  1 

It works and I get the expected output:

3           2015-06-24 10:47:18

I need some better solution. I do not like the sub query. The table and query above is just for simple example. The original detailed business logic and query is way more complex. So I want to make this part simpler if possible.

The main issue is when GROUP BY (in sql comment) is applied it does not honor the ORDER BY clause. In other words, the GROUP BY is applied before applying the ordering. To overcome this I do a order by booking_time DESC in sub query, then apply the group by and order it back by booking_time ASC.

Is it possible to achieve it without doing a sub query?

Answer 1

please try this query:

select driver_id, max(booking_time) m 
from bookings 
group by driver_id 
order by count(*),m 
limit 1;