Problems finding the MAX value
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. | | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| Anne | 5. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (30c1)| Anne | 10. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 3. | | 2 dozen chocolate chip cookies (a3aa)| Linda | 4. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 5. | | 2 dozen chocolate chip cookies (a3aa)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl | 2. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 3. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl | 5. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | Mandy | 2. | | 2 Gold Rings (d9c1) | Isabelle | 3. | | 2 Gold Rings (d9c1) | Hugh | 4. | | 2 Gold Rings (d9c1) | Isabelle | 5. | | 2 Gold Rings (d9c1) | Hugh | 6. | | 2 Gold Rings (d9c1) | Roslyn | 7. | | 2 Gold Rings (d9c1) | Hugh | 8. | | 2 Gold Rings (d9c1) | Roslyn | 10. | | 2 Gold Rings (d9c1) | Hugh | 12. | | 2 Gold Rings (d9c1) | Roslyn | 15. | | 2 Gold Rings (d9c1) | Hugh | 16. | | 2 Gold Rings (d9c1) | Linda | 20. | | 2 Gold Rings (d9c1) | Hugh | 21. | | 2 Gold Rings (d9c1) | t | 22. | | 2 Gold Rings (d9c1) | Hugh | 23. | | 2 Gold Rings (d9c1) | Linda | 25. | | 2 Gold Rings (d9c1) | t | 26. | | 2 piece outfit (purple and white) (d60d) | Mandy | 1. | | 2 piece outfit (purple and white) (d60d) | Susan | 3. | +--+---+-+ 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
Re: Problems finding the MAX value
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. | | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| Anne | 5. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (30c1)| Anne | 10. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 3. | | 2 dozen chocolate chip cookies (a3aa)| Linda | 4. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 5. | | 2 dozen chocolate chip cookies (a3aa)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl| 2. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 3. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl| 5. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | Mandy | 2. | | 2 Gold Rings (d9c1) | Isabelle | 3. | | 2 Gold Rings (d9c1) | Hugh | 4. | | 2 Gold Rings (d9c1) | Isabelle | 5. | | 2 Gold Rings (d9c1) | Hugh | 6. | | 2 Gold Rings (d9c1) | Roslyn| 7. | | 2 Gold Rings (d9c1) | Hugh | 8. | | 2 Gold Rings (d9c1) | Roslyn| 10. | | 2 Gold Rings (d9c1) | Hugh | 12. | | 2 Gold Rings (d9c1) | Roslyn| 15. | | 2 Gold Rings (d9c1) | Hugh | 16. | | 2 Gold Rings (d9c1) | Linda | 20. | | 2 Gold Rings (d9c1) | Hugh | 21. | | 2 Gold Rings (d9c1) | t | 22. | | 2 Gold Rings (d9c1) | Hugh | 23. | | 2 Gold Rings (d9c1) | Linda | 25. | | 2 Gold Rings (d9c1) | t | 26. | | 2 piece outfit (purple and white) (d60d) | Mandy | 1. | | 2 piece outfit (purple and white) (d60d) | Susan | 3. | +-- +---+-+ 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
Re: Problems finding the MAX value
[EMAIL PROTECTED] wrote on 11/04/2005 11:22:35 AM: This is such a FAQ that they put the answer in the manual: http://dev.mysql.com/doc/refman/5.0/en/index.html OOPS! I copied the wrong link. It should have been: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html Sorry all! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problems finding the MAX value
Man, you're awesome. Only two typos in the whole procedure. However, for the life of me, I'm puzzled over how it works. If you have more patience can you explain? Correct query (table is singular, not PHPAUCTIONS_...): CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTION_bids GROUP BY auction; The temporary table tmpWinners doesn't even contain a field for the ID of the winning bidder. This is the first puzzling point and significant diference between our two solutions. Correct query (changed line 8 from AND b.bit...): SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bid = 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 5; - \g +--+---+-+ | FullTitle| fname | bid | +--+---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | t | 26. | +--+---+-+ 5 rows in set (0.15 sec) mysql So, the SELECT query goes through the tmpWinniing table and, for each record, finds a record in the bids table that has the same item ID and same bid price. It then uses the bidder's ID it found to look up the bidder's name, and the auction item's ID to look up the item's title. It seems to me that the significant difference between our solutions is your use of two conditional clauses in the INNER JOIN between tmpWinner and PHPAUCTION_bids. I don't think I've ever seen a join done on more than one field between two tables before. Would this query still work if more than one person bid the same amount on the same item? The business rules built into phpAuction prevent this, but in a more generic situation, would this query still work correctly? I guess one anwer to this is 'yes,' because the MAX() function returns the first of two equal maximum values it finds, doesn't it? Despite my puzzlement at how you were able to come up with such a great solution, I'm very grateful for your help and explanations. If you're ever in Baltimore, MD, I owe you a beer. Thanks. -Kevin [EMAIL PROTECTED] 11/04/05 11:22AM 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 FROMtmpWinners 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems finding the MAX value
How it works... KEVIN ZEMBOWER [EMAIL PROTECTED] wrote on 11/04/2005 12:40:50 PM: Man, you're awesome. Only two typos in the whole procedure. However, for the life of me, I'm puzzled over how it works. If you have more patience can you explain? Correct query (table is singular, not PHPAUCTIONS_...): CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTION_bids GROUP BY auction; The temporary table tmpWinners doesn't even contain a field for the ID of the winning bidder. This is the first puzzling point and significant diference between our two solutions. The CREATE TABLE command can make tables out of query results. No need to define the columns as they will be auto-typed to fit the results. In this case I chose to create a TEMPORARY table instead of a static table. http://dev.mysql.com/doc/refman/4.1/en/create-table.html Correct query (changed line 8 from AND b.bit...): SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bid = 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 5; - \g +-- +---+-+ | FullTitle| fname | bid | +-- +---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | t | 26. | +-- +---+-+ 5 rows in set (0.15 sec) mysql So, the SELECT query goes through the tmpWinniing table and, for each record, finds a record in the bids table that has the same item ID and same bid price. It then uses the bidder's ID it found to look up the bidder's name, and the auction item's ID to look up the item's title. exactly. It seems to me that the significant difference between our solutions is your use of two conditional clauses in the INNER JOIN between tmpWinner and PHPAUCTION_bids. I don't think I've ever seen a join done on more than one field between two tables before. Would this query still work if more than one person bid the same amount on the same item? The business rules built into phpAuction prevent this, but in a more generic situation, would this query still work correctly? I guess one anwer to this is 'yes,' because the MAX() function returns the first of two equal maximum values it finds, doesn't it? You have surmised correctly. If more than one row matches your maximum condition (as determined by the rows in tmpWinners) then all matching rows will be returned in the final query. Despite my puzzlement at how you were able to come up with such a great solution, I'm very grateful for your help and explanations. If you're ever in Baltimore, MD, I owe you a beer. Thanks. -Kevin SQL is a language that tends towards patterns. Learn the patterns and you get better at SQL. This happens to be a very well-known pattern so I can't take any of the credit as it was around long before I started programming. I am just glad I could share. Do I still get that beer? ;-) About multiple terms in the ON phrases of the JOIN clauses... It is sometimes to your advantage to move terms out of the WHERE clause and into the ON clause (especially when it comes to the LEFT and RIGHT joins). Not only can it make the logic of your query correct but sometimes it opens the optimizer up to using indexes it may not have considered before. Almost any logical statement is permissible in an ON clause. See http://dev.mysql.com/doc/refman/5.0/en/join.html for more details on what will and won't work based on the order your tables are JOINed into the query. Sorry about the typos. Glad they didn't mess you up :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
