SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Aug 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY HAVING not working for my query

    Code:
    SELECT * FROM `order_offer` WHERE `order_id` = '1131201' GROUP BY `offer_id` HAVING `offer_version` = MAX(offer_version)
    What I am trying to do here is pull all of the offers here related to the order ID above, but unique to ONE offer_id with the max offer_version.

    Unfortunately, this isn't working at all. If you have any suggestions, I would greatly appreciate it. I was thinking of just figuring out the solution with php(I am not well versed in sql), but I should probably do it right.

    For each order we make an offer per product in the basket. That way we can modify each items pricing and keep the previous record. It is all for records.

    Thanks for the help!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT * 
    FROM order_offer 
    INNER JOIN
      (SELECT
           order_id
         , offer_id
         , MAX(offer_version) as maxversion
       FROM order_offer 
       WHERE order_id = '1131201' 
       GROUP BY order_id, offer_id
      ) AS max_offer
    WHERE order_offer.order_id = max_offer.order_id
    AND   order_offer.offer_id = max_offer.offer_id
    AND   order_offer.offer_version = max_offer.maxversion
    Last edited by guido2004; Aug 22, 2012 at 00:12. Reason: corrected error in query

  3. #3
    SitePoint Member
    Join Date
    Aug 2012
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I changed the last line from

    Code:
    AND   order_offer.offer_version = max_offer.offer_version
    to
    Code:
    AND   order_offer.offer_version = max_offer.maxversion
    and this worked like a charm. I really thought it was easier than this. Thanks so much for the solution!

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by nickmccally View Post
    I changed the last line from

    Code:
    AND   order_offer.offer_version = max_offer.offer_version
    to
    Code:
    AND   order_offer.offer_version = max_offer.maxversion
    and this worked like a charm.
    Ah yes. I corrected the query in my post.


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
  •