List:General Discussion« Previous MessageNext Message »
From:KEVIN ZEMBOWER Date:November 4 2005 5:40pm
Subject:Re: Problems finding the MAX value
View as plain text  
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
FROM    tmpWinners 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.0000 |
| 2 dozen chocolate chip cookies (30c1)                        | ucantoutbidme | 11.0000 |
| 2 dozen chocolate chip cookies (a3aa)                        | Donna         |  7.0000 |
| 2 dozen chocolate chip cookies (d8e5)                        | Donna         |  8.0000 |
| 2 Gold Rings (d9c1)                                          | t             | 26.0000 |
+--------------------------------------------------------------+---------------+---------+
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

>>> <SGreen@stripped> 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 
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

Thread
Problems finding the MAX valueKEVIN ZEMBOWER4 Nov
  • Re: Problems finding the MAX valueSGreen4 Nov
    • Re: Problems finding the MAX valueSGreen4 Nov
Re: Problems finding the MAX valueKEVIN ZEMBOWER4 Nov
Re: Problems finding the MAX valueKEVIN ZEMBOWER4 Nov
  • Re: Problems finding the MAX valueSGreen4 Nov