On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
> Hi
>
> I am trying to write an auction script that uses mysql as its backend. Each
> auction can have upto 25 sub auctions(cells) taking place.
>
> I'm trying to query the DB to give me a list of all the successfull bids
> for each cell, for each auction...
>
> SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
> b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
> auctionId,cellId ORDER BY bidTime DESC
>
> This is further complicated in so much that multiple MAX bids may exist at
> the same value, but, only the earliest should be returned for each cell.
>
> Which is returning some of the columns correctly, namely auctionid, cellid
> and bid, but it does not return the bidderId correctly.
>
> Can anybody help?
Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.
Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.
In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
>
> Cheers
>
> Carl
>
>
> #
> # Table structure for table `sa_auction`
> #
>
> CREATE TABLE sa_auction (
> id int(11) NOT NULL auto_increment,
> start datetime NOT NULL default '0000-00-00 00:00:00',
> end datetime NOT NULL default '0000-00-00 00:00:00',
> state enum('waiting','active','expired') NOT NULL default 'waiting',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> #
> # Dumping data for table `sa_auction`
> #
>
> INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
> 21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
> start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
> 'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
> '2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
> sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
> '2002-08-01 11:30:00', 'waiting'); #
> --------------------------------------------------------
>
> #
> # Table structure for table `sa_bid`
> #
>
> CREATE TABLE sa_bid (
> id int(11) NOT NULL auto_increment,
> auctionId int(11) NOT NULL default '0',
> cellId tinyint(4) NOT NULL default '0',
> bid int(11) NOT NULL default '0',
> bidderId mediumint(9) NOT NULL default '0',
> bidtime timestamp(14) NOT NULL,
> PRIMARY KEY (id),
> UNIQUE KEY id (id),
> KEY id_2 (id)
> ) TYPE=MyISAM;
>
> #
> # Dumping data for table `sa_bid`
> #
>
> INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES
> (1, 1, 5, 1, 1, 20020128225421); INSERT INTO sa_bid (id, auctionId, cellId,
> bid, bidderId, bidtime) VALUES (2, 1, 5, 2, 2, 20020128225424); INSERT INTO
> sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
> 15, 2, 20020213214856); INSERT INTO sa_bid (id, auctionId, cellId, bid,
> bidderId, bidtime) VALUES (4, 1, 5, 3, 4, 20020213215649);
>
>
>
> ------------
> This e-mail and any attachments are confidential. If you are not the
> intended recipient, please notify us immediately by reply e-mail and then
> delete this message from your system. Do not copy this e-mail or any
> attachments, use the contents for any purpose, or disclose the contents to
> any other person: to do so could be a breach of confidence.
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
