SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)

    Getting Highest Bid and Bidder

    I'm having trouble getting a correlated subquery to work (it's the first time that I've used correlated subqueries). There's two tables, one records auctions and the other records bids. Not all auctions will have any bids, the intended output will be three columns, auction_id, bidder (the ID of the highest bidder else 0) and the highest bid (with 0 being returned if there were no bids).

    The idea being that the current tick gets used in the main WHERE CLAUSE to get all auctions that end that tick and for each one get the info mentioned above. The query I've currently got is:

    Code SQL:
                  SELECT
                      CASE WHEN
                        bids.bidder IS NULL
                              THEN 0
                              ELSE bids.bidder
                          END AS bidder                    
                    , CASE WHEN
                        bids.gold_bid IS NULL
                              THEN 0
                              ELSE bids.gold_bid
                          END AS gold_bid
                      , auction.id AS auction_id                  
                  FROM
                      ue_auction_bids AS bids
                  RIGHT OUTER JOIN
                      ue_auction AS auction
                          ON bids.auction_id = auction.id
                  WHERE
                      gold_bid = 
                          (
                              SELECT
                                  MAX(gold_bid) AS max_bid
                              FROM
                                  ue_auction_bids AS bids
                              WHERE
                                  auction_id = bids.auction_id
                          )
                  AND
                      auction.end_tick = 50

    That gives me,
    Code:
    bidder gold_bid auction_id
    2       153410         1
    I'm not sure if it's possible to do it in 1 query (I want to try and do it in one query to having to send unnecessary queries between PHP and MySQL, I know that both will in reality be on the same server box).

    Table structure and test data for both tables

    Code SQL:
    CREATE TABLE IF NOT EXISTS `ue_auction` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `con_lister` INT(11) NOT NULL,
      `start_gold` INT(11) NOT NULL,
      `start_tick` INT(11) NOT NULL,
      `end_tick` INT(11) NOT NULL,
      `comments` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
     
    --
    -- Dumping data for table `ue_auction`
    --
     
    INSERT INTO `ue_auction` (`id`, `con_lister`, `start_gold`, `start_tick`, `end_tick`, `comments`) VALUES
    (1, 3, 200, 1, 50, ''),
    (2, 3, 200, 1, 50, ''),
    (3, 3, 200, 1, 50, '');
     
    CREATE TABLE IF NOT EXISTS `ue_auction_bids` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `auction_id` INT(11) NOT NULL,
      `gold_bid` INT(11) NOT NULL,
      `bidder` INT(11) NOT NULL,
      `bid_tick` INT(11) NOT NULL,
      `bid_date` datetime NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;
     
    --
    -- Dumping data for table `ue_auction_bids`
    --
     
    INSERT INTO `ue_auction_bids` (`id`, `auction_id`, `gold_bid`, `bidder`, `bid_tick`, `bid_date`) VALUES
    (1, 1, 200, 1, 2, '2011-03-07 19:53:56'),
    (2, 1, 230, 0, 5, '2011-03-13 08:49:27'),
    (3, 1, 265, 1, 5, '2011-03-13 08:53:11'),
    (4, 1, 500, 1, 5, '2011-03-13 08:54:04'),
    (5, 1, 575, 1, 5, '2011-03-13 08:54:33'),
    (6, 1, 662, 1, 5, '2011-03-13 08:55:10'),
    (7, 1, 762, 1, 5, '2011-03-13 09:03:23'),
    (8, 1, 877, 1, 5, '2011-03-14 02:22:15'),
    (9, 1, 1009, 1, 5, '2011-03-14 02:22:30'),
    (10, 1, 1161, 1, 5, '2011-03-14 02:23:07'),
    (11, 1, 1336, 1, 5, '2011-03-14 02:23:37'),
    (12, 1, 1537, 1, 5, '2011-03-14 02:23:48'),
    (13, 1, 1768, 1, 5, '2011-03-14 02:24:00'),
    (14, 1, 2034, 1, 5, '2011-03-14 02:24:25'),
    (15, 1, 2340, 0, 5, '2011-03-14 08:18:30'),
    (16, 1, 2691, 0, 5, '2011-03-14 08:18:40'),
    (17, 1, 3095, 1, 5, '2011-03-14 08:21:17'),
    (18, 1, 4000, 1, 5, '2011-03-14 08:21:26'),
    (19, 1, 5000, 1, 5, '2011-03-14 08:21:30'),
    (20, 1, 6000, 2, 5, '2011-03-16 19:43:50'),
    (21, 1, 70000, 2, 5, '2011-03-16 19:43:57'),
    (22, 1, 100000, 2, 5, '2011-03-16 19:44:18'),
    (23, 1, 116000, 1, 5, '2011-03-21 08:44:22'),
    (24, 1, 133400, 1, 5, '2011-03-21 08:44:38'),
    (25, 1, 153410, 2, 5, '2011-03-23 19:04:19');
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    four things, two of them minor

    1. your CASE expressions can be simplified with COALESCE

    2. you should, for the sake of your own sanity and that of anyone working on your queries, rewrite all your RIGHT OUTER JOINs as LEFT OUTER JOINs

    3. your WHERE clause on a column of the bids table effectively converted your outer join to an inner join

    4. correlated subqueries are notoriously inefficient, you should use non-correlated when possible

    try this --
    Code:
    SELECT COALESCE(bids.bidder,0) AS bidder
         , COALESCE(bids.gold_bid,0) AS gold_bid
         , auction.id AS auction_id
      FROM ue_auction AS auction
    LEFT OUTER
      JOIN ( SELECT auction_id
                  , MAX(gold_bid) AS max_bid
               FROM ue_auction_bids
             GROUP
                 BY auction_id ) AS m
        ON m.auction_id = auction.id
    LEFT OUTER
      JOIN ue_auction_bids AS bids
        ON bids.auction_id = m.auction_id
       AND bids.gold_bid   = m.max_bid
     WHERE auction.end_tick = 50
    results --
    Code:
    bidder  gold_bid  auction_id
       2    153410       1
       0         0       2
       0         0       3
    on your data, my query executed in 0.062 sec

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

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    four things, two of them minor

    1. your CASE expressions can be simplified with COALESCE

    2. you should, for the sake of your own sanity and that of anyone working on your queries, rewrite all your RIGHT OUTER JOINs as LEFT OUTER JOINs

    3. your WHERE clause on a column of the bids table effectively converted your outer join to an inner join

    4. correlated subqueries are notoriously inefficient, you should use non-correlated when possible

    try this --
    Code:
    SELECT COALESCE(bids.bidder,0) AS bidder
         , COALESCE(bids.gold_bid,0) AS gold_bid
         , auction.id AS auction_id
      FROM ue_auction AS auction
    LEFT OUTER
      JOIN ( SELECT auction_id
                  , MAX(gold_bid) AS max_bid
               FROM ue_auction_bids
             GROUP
                 BY auction_id ) AS m
        ON m.auction_id = auction.id
    LEFT OUTER
      JOIN ue_auction_bids AS bids
        ON bids.auction_id = m.auction_id
       AND bids.gold_bid   = m.max_bid
     WHERE auction.end_tick = 50
    results --
    Code:
    bidder  gold_bid  auction_id
       2    153410       1
       0         0       2
       0         0       3
    on your data, my query executed in 0.062 sec

    ty Rudy, that worked perfectly!

    With them four points:

    1) There's a couple of other queries where I'd used CASE where I'll now switch them to use COALESCE.

    2) I do normally use LEFT OUTER JOINs, the use of RIGHT OUTER JOIN came about from the attempt to use the correlated subquery.

    3) I guess that mistake came about by the the attempt to use the correlated subquery.

    4) I'll have to remember that.

    Locally your query ran in 0.0008 sec i guess that's just down to the differences in hardware config.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


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
  •