Getting the Latest Child Record

[usachrisk]

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:

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...

[longneck]

that is the most efficient way to accomplish what you need, except i would change "in" to "="

[r937]

faster still --

select a.product_name
     , b.order_number
  from a
inner
  join b
    on b.product_id = a.product_id
inner
  join ( select product_id
              , max(order_date) as maxdate 
           from b
         group
             by product_id ) as bmax
    on bmax.product_id = a.product_id
   and bmax.maxdate = b.order_date

[usachrisk]

that is the most efficient way to accomplish what you need, except i would change "in" to "="

Historically I've used "IN" instead of "=" so that if there happens to be a duplicate I don't get an Oracle error about my subquery having more than one row that matches - most of the time I'd rather just see the "order" placed at the same time than a failure.

Rudy - thanks - let me try and fit your example into my query!

[usachrisk]

faster still --

select a.product_name
     , b.order_number
  from a
inner
  join b
    on b.product_id = a.product_id
inner
  join ( select product_id
              , max(order_date) as maxdate 
           from b
         group
             by product_id ) as bmax
    on bmax.product_id = a.product_id
   and bmax.maxdate = b.order_date

Run my way - 5,476 rows returned in 25s.
Run Rudy's way - 5,476 rows returned in 313ms

Wow. Tested a few times to make sure there weren't any neat little Oracle caching things helping out - the numbers were pretty consistent. Faster Still my @$$ I have so many queries to go find & rewrite

[r937]

Historically I've used "IN" instead of "=" so that if there happens to be a duplicate ...

whoa, stop right there

WHERE something = ( MAX(something) ... )

how many different maximum values are you expecting?

there can be only one max value

aggregate functions deal with values, not rows

[r937]

Faster Still my @$$

good thing i don't take stuff like that personally

i'm sure you will find out in your own time, maybe not on this query, maybe not on the next one, but the day will come when you remember the join to a derived group instead of the correlated subquery...

all i'm asking is that you drop me a line then and say "you were right"

[usachrisk]

whoa, stop right there

WHERE something = ( MAX(something) ... )

how many different maximum values are you expecting?

there can be only one max value

aggregate functions deal with values, not rows

You're absolutely right, very poor example on my part

[usachrisk]

Rudy, I think you misunderstood. Crazy Internet. My way was 25 seconds and your way was 313 milliseconds. You're already right- you've already wowed me My "faster still" remark was meant the other way, I guess SitePoint doesn't yet support conveying the tone I meant into the forums As in, it's just "faster still", it killed my query, I took my query and blew it all to hell. You win

[r937]

actually, it's me -- i can't read

25s versus 313ms

i misled myself

[usachrisk]

Well I'm glad we've got it all straightened out - I'd hate for you to come find my trailer in Texas

[r937]

it's only a 40 day ride...