2

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   |
Strawberry
  • 33,750
  • 13
  • 40
  • 57
jboo
  • 21
  • 1
  • 1
  • 3
  • I obviously failed pretty hard on the automated formatting here, trying to fix it, sorry. – jboo Feb 21 '15 at 16:50
  • You talk about grouping in the beginning, but then don't want the grouping in the result? I'm not clear why you only want the `12` item? – Wain Feb 21 '15 at 16:52

1 Answers1

1

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();
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786