Hi,
At 11:21 AM 14/02/2002 -0500, you wrote:
>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.
This behaviour is well documented in the manual. There is a very practical
reason to allow this behaviour. Suppose for efficiency reasons data is
denormalized
and for example, id,name and some other particulars are all kept in the same
table . If the database was very strict that all the columns selected
should be
in the group by expression, one will have to put all these columns (id,name,..)
in the group by clause. Then the db engine will have to take all these
fields in
the intermediate ordering phase of the query execution. Surely this will be
inefficient in both time and space. But with the 'incorrect' behaviour of
Mysql
one can put all the data columns required to be returned in the select and do
group by only the id. This would make the query to complete very fast compared
to the former and the effect will be even more pronounced with index on id
field.
>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
The problem can be solved by using temporary tables.
Create temporary table tmp1
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid FROM sa_bid as b,
sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ;
Create temporary table tmp2
Select t1.auctionid, t1.cellid, t1.bid, min(b.bidtime) as bidtime from tmp1
as t1, sa_bid as b
where (t1.auctionid = b.auctionid and t1.cellid = b.cellid and t1.bid =
b.bid)
group by t1.auctionid,t1.cellid,t1.bid
Select t2.*, b.bidderid from tmp2 as t2, sa_bid as b
where t2.auctionid = b.auctionid and t2.cellid = b.cellid and t2.bid =
b.bid and t2.bidtime = b.bidtime
I hope there may be better and simpler ways to achieve the objective.
Surely correlated subquery and derived table features might have been good
features for such
situations.
Anvar.
>
> > #
> > # 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);
> >
> >
> >
---------------------------------------------------------------------
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
