Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Your query seems correct. It would return more than one row per booking_id only if you have rows with same last_update_time. –  ypercube Jun 13 '13 at 18:31
add comment

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
Perfect, thanks –  muttonlamb Jun 13 '13 at 23:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.