Getting the Latest Child Record
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:
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.
on a.product_id = b.product_id
and b.order_date in
( select max(b.order_date)
where b.product_id = a.product_id
Open to any suggestions...