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