My organization runs an online auction on our intranet server for the United
Way. I'm having a hard time printing out a list of the winning bidders (I'm
under a lot of pressure; the baked goods are getting stale).
In these examples, I've just printed out my bidder's first names, to protect
their privacy.
This output seems correct. I could just manually scan this for the maximum
value for each item:
mysql> SELECT CONCAT(a.title, " (", LEFT(b.auction,4), ")") AS FullTitle,
SUBSTRING_INDEX(u.name, " ", 1) AS fname, b.bid FROM PHPAUCTION_bids AS b,
PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE b.bidder=u.id AND
b.auction=a.id ORDER BY FullTitle LIMIT 35;
+--------------------------------------------------------------+---------------+---------+
| FullTitle | fname
| bid |
+--------------------------------------------------------------+---------------+---------+
| 101 Dalmations, Oliver & Company, A Goofy Movie - VHS (d7b4) | Lisa
| 2.0000 |
| 101 Dalmations, Oliver & Company, A Goofy Movie - VHS (d7b4) | Saori
| 3.0000 |
| 2 dozen chocolate chip cookies (30c1) | Anne
| 5.0000 |
| 2 dozen chocolate chip cookies (30c1) | ucantoutbidme
| 6.0000 |
| 2 dozen chocolate chip cookies (30c1) | Anne
| 10.0000 |
| 2 dozen chocolate chip cookies (30c1) | ucantoutbidme
| 11.0000 |
| 2 dozen chocolate chip cookies (a3aa) | Donna
| 3.0000 |
| 2 dozen chocolate chip cookies (a3aa) | Linda
| 4.0000 |
| 2 dozen chocolate chip cookies (a3aa) | Donna
| 5.0000 |
| 2 dozen chocolate chip cookies (a3aa) | ucantoutbidme
| 6.0000 |
| 2 dozen chocolate chip cookies (a3aa) | Donna
| 7.0000 |
| 2 dozen chocolate chip cookies (d8e5) | Cheryl
| 2.0000 |
| 2 dozen chocolate chip cookies (d8e5) | ucantoutbidme
| 3.0000 |
| 2 dozen chocolate chip cookies (d8e5) | Cheryl
| 5.0000 |
| 2 dozen chocolate chip cookies (d8e5) | ucantoutbidme
| 6.0000 |
| 2 dozen chocolate chip cookies (d8e5) | Donna
| 8.0000 |
| 2 Gold Rings (d9c1) | Mandy
| 2.0000 |
| 2 Gold Rings (d9c1) | Isabelle
| 3.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 4.0000 |
| 2 Gold Rings (d9c1) | Isabelle
| 5.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 6.0000 |
| 2 Gold Rings (d9c1) | Roslyn
| 7.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 8.0000 |
| 2 Gold Rings (d9c1) | Roslyn
| 10.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 12.0000 |
| 2 Gold Rings (d9c1) | Roslyn
| 15.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 16.0000 |
| 2 Gold Rings (d9c1) | Linda
| 20.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 21.0000 |
| 2 Gold Rings (d9c1) | t
| 22.0000 |
| 2 Gold Rings (d9c1) | Hugh
| 23.0000 |
| 2 Gold Rings (d9c1) | Linda
| 25.0000 |
| 2 Gold Rings (d9c1) | t
| 26.0000 |
| 2 piece outfit (purple and white) (d60d) | Mandy
| 1.0000 |
| 2 piece outfit (purple and white) (d60d) | Susan
| 3.0000 |
+--------------------------------------------------------------+---------------+---------+
35 rows in set (0.06 sec)
mysql>
Note that there were actually three different batches of "2 dozen chocolate
chip cookies." I printed the internal ID numbers to distinguish them.
However, when I try to get fancy and print out just the winners, the winning
amount comes out, but with the name of the FIRST, not the winning, bidder:
mysql> SELECT CONCAT(a.title, " (", LEFT(b.auction,6), ")") as FullTitle,
SUBSTRING_INDEX(u.name, " ", 1) AS fname, MAX(b.bid) FROM PHPAUCTION_bids AS b,
PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE b.bidder=u.id AND
b.auction=a.id GROUP BY FullTitle ORDER BY FullTitle LIMIT 5;
+----------------------------------------------------------------+--------+------------+
| FullTitle | fname |
MAX(b.bid) |
+----------------------------------------------------------------+--------+------------+
| 101 Dalmations, Oliver & Company, A Goofy Movie - VHS (d7b448) | Lisa |
3.0000 |
| 2 dozen chocolate chip cookies (30c106) | Anne |
11.0000 |
| 2 dozen chocolate chip cookies (a3aa96) | Donna |
7.0000 |
| 2 dozen chocolate chip cookies (d8e539) | Cheryl |
8.0000 |
| 2 Gold Rings (d9c17f) | Mandy |
26.0000 |
+----------------------------------------------------------------+--------+------------+
5 rows in set (0.08 sec)
mysql>
In this example, I think that the only reason the second batch of "2 dozen
chocolate chip cookies" winner is correct is because she was also the first
bidder.
I even tried to create a separate table with just the winners and work from
that, but with the wrong results:
mysql> CREATE TABLE winners SELECT auction, bidder, MAX(bid) AS winningbid FROM
PHPAUCTION_bids GROUP BY auction;
Query OK, 205 rows affected (0.01 sec)
Records: 205 Duplicates: 0 Warnings: 0
mysql> SELECT CONCAT(a.title, "(", LEFT(w.auction,6), ")") AS itemname,
SUBSTRING_INDEX(u.name, " ", 1) AS fname, winningbid FROM winners AS w,
PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE w.auction=a.id AND
w.bidder=u.id ORDER BY itemname LIMIT 5;
+---------------------------------------------------------------+--------+------------+
| itemname | fname |
winningbid |
+---------------------------------------------------------------+--------+------------+
| 101 Dalmations, Oliver & Company, A Goofy Movie - VHS(d7b448) | Lisa |
3.0000 |
| 2 dozen chocolate chip cookies(30c106) | Anne |
11.0000 |
| 2 dozen chocolate chip cookies(a3aa96) | Donna |
7.0000 |
| 2 dozen chocolate chip cookies(d8e539) | Cheryl |
8.0000 |
| 2 Gold Rings(d9c17f) | Mandy |
26.0000 |
+---------------------------------------------------------------+--------+------------+
5 rows in set (0.03 sec)
mysql>
I think the way to do this is with a nested SELECT, but I couldn't make any of
my attempts work. Could someone please help me construct an SQL query which
would just print out the correct winners for each auction? I'm using MySQL
4.0.24 in Debian sarge.
Thanks for your advice and suggestions.
-Kevin Zembower
-----
E. Kevin Zembower
Internet Systems Group manager
Johns Hopkins University
Bloomberg School of Public Health
Center for Communications Programs
111 Market Place, Suite 310
Baltimore, MD 21202
410-659-6139
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
