Getting Highest Bid and Bidder

[SpacePhoenix]

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:

              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,

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

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');

[r937]

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

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

bidder  gold_bid  auction_id
   2    153410       1
   0         0       2
   0         0       3

on your data, my query executed in 0.062 sec

[SpacePhoenix]

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

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

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.