I'm trying to figure out how to write this query.
Table 1 - Booking
BOOKING_ID | BOOKING_DATE | etc... 1 | 2013/01/01 | 2 | 2013/01/01 |
Table 2 - BOOKING_PROCEDURE_TYPE
BOOKING_PROCEDURE_TYPE_ID | BOOKING_ID | LAST_UPDATE_TIME | COMMENTS 1 | 1 | 10:00 | blah blah 2 | 1 | 08:45 | 3 | 1 | 06:00 | referred by
What I want is to join table two where the booking ID matches, but only include the most recent update for each row.
I've written the query 20 different ways, but I keep ending up with a row for every instance of BOOKING_ID.
This is the closest I've gotten so far
SELECT bpt.* FROM pas.BOOKING_PROCEDURE_TYPE bpt INNER JOIN (SELECT b.BOOKING_ID, MAX(b.LAST_UPDATE_TIME) as LUP FROM pas.BOOKING_PROCEDURE_TYPE b GROUP BY b.BOOKING_ID)x on bpt.BOOKING_ID = x.BOOKING_ID AND bpt.LAST_UPDATE_TIME = x.LUP
I know I'm probably making some really obvious stuff up and would appreciate some help from better people than I