Joining data from legacy database

Question

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

Answer 1

You can use ROW_NUMBER (Transact-SQL) to enumerate the rows in BOOKING_PROCCEDURE_TYPE partitioned by BOOKING_ID and ordered by LAST_UPDATE_TIME descending.

select *
from BOOKING as B
  inner join (
              select *,
                     row_number() over(partition by BOOKING_ID 
                                       order by LAST_UPDATE_TIME desc) as rn
              from BOOKING_PROCCEDURE_TYPE
             ) as BPT
    on B.BOOKING_ID = BPT.BOOKING_ID
where BPT.rn = 1