CODEWITHSUNDEEP
mysqldatesubquerymax
jboo
jboo

Reputation: 21

Select max date in a group where max date is less than current date

I'm trying to execute a mysql query on this table data that would return the latest date that is less than the current date grouped by the booking number.

Example of the table data: 

bookingnumber | booking_date |
------------------------------
11            | 2015-02-21   |
11            | 2015-02-22   |
11            | 2015-02-20   |
12            | 2015-02-20   |   
13            | 2015-02-22   |
------------------------------

If current date is 2015-02-21 I am striving to get this result:

bookingnumber | booking_date |
------------------------------
12            | 2015-02-20   |

I have experimented with MAX, subquerys, and groups and this is the "best" I have managed to do so far:

"select bookingnumber, booking_date 
FROM table 
WHERE 
(select max(booking_date) from table as f where f.bookingnumber = table.bookingnumber) 
AND booking_date < CURRENT_DATE"

This however gives me the result:

bookingnumber | booking_date |    
------------------------------
12            | 2015-02-20   |   
11            | 2015-02-20   |

Upvotes: 2

Views: 16248

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The following returns the results you are asking for. It is returning the last date of bookings for which there is no current or future booking date:

SELECT bookingnumber, max(booking_date)
FROM table
GROUP BY bookingnumber
HAVING max(booking_date) < CURRENT_DATE();

Upvotes: 1

Related Questions