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
