SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Jul 2005
    Posts
    609
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

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

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    that is the most efficient way to accomplish what you need, except i would change "in" to "="
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    faster still --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Jul 2005
    Posts
    609
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    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!

  5. #5
    SitePoint Guru
    Join Date
    Jul 2005
    Posts
    609
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    faster still --
    Code:
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by usachrisk View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by usachrisk View Post
    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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Jul 2005
    Posts
    609
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  9. #9
    SitePoint Guru
    Join Date
    Jul 2005
    Posts
    609
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    actually, it's me -- i can't read

    25s versus 313ms

    i misled myself

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Jul 2005
    Posts
    609
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I'm glad we've got it all straightened out - I'd hate for you to come find my trailer in Texas

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it's only a 40 day ride...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •