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?
please try this query: