"KEVIN ZEMBOWER" <[EMAIL PROTECTED]> wrote on 11/04/2005 11:05:05 AM:
> 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
>
This is such a FAQ that they put the answer in the manual:
http://dev.mysql.com/doc/refman/5.0/en/index.html
What you are looking for is the row that contains the maximum bid for each
itemname. The easiest first step it to actually determine what the highest
bid for each item actually is then use that information to build the rest
of what you wanted.
The most portable and easiest to write solution to this is the two-table
method ( I think I divined your column names correctly, maybe not...):
# begin example #
CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid
FROM PHPAUCTIONS_bids GROUP BY auction;
SELECT
CONCAT(a.title, " (", LEFT(b.auction,4), ")") AS FullTitle
, SUBSTRING_INDEX(u.name, " ", 1) AS fname
, b.bid
FROM tmpWinners AS w
INNER JOIN PHPAUCTION_bids AS b
ON b.auction = w.auction
AND b.bit = w.winningbid
INNER JOIN PHPAUCTION_users AS u
ON b.bidder=u.id
INNER JOIN PHPAUCTION_auctions AS a
ON b.auction=a.id
ORDER BY FullTitle
LIMIT 35;
DROP TEMPORARY TABLE tmpWinners;
# end example #
Does that make sense? You should be able to expand on that pattern to
build whatever list you want. I showed the full-chain of how each table
relates to another but you could have simplified the query above, can you
see where?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
