Hi All -

I'm looking for the best... erm, ... fastest (run time) ... way to get only the "latest" child record from a table when joining it. Example: Table a has products and Table b has orders. Currently, if I want to list all products with orders and the last order I would do something like:

Code:
select a.product_name
     , b.order_number
  from a
  join b
    on a.product_id = b.product_id
   and b.order_date in 
     ( select max(b.order_date) 
         from b
        where b.product_id = a.product_id
     )
Untested, but I'm sure you get the idea. "A" would join "B" only when the product IDs matched and when the order date was the latest order. In this situation, I'd probably get duplicates if a product was ordered more than once at the same time, but that can't happen in my real world situation so I ignore it here. This seems slow and I tend to think there might be a better way, but I'm not sure how else I'd do it.

Open to any suggestions...