From: KEVIN ZEMBOWER
Date: November 4 2005 5:40pm
Subject: Re: Problems finding the MAX value
List-Archive: http://lists.mysql.com/mysql/191270
Message-Id: <s36b56d8.004@jhuccp.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

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=20=

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 =3D w.auction
        AND b.bid =3D w.winningbid
INNER JOIN PHPAUCTION_users AS u
        ON b.bidder=3Du.id
INNER JOIN PHPAUCTION_auctions AS a
        ON b.auction=3Da.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)                        | ucantoutbi=
dme | 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>=20

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:=20
http://dev.mysql.com/doc/refman/5.0/en/index.html=20

What you are looking for is the row that contains the maximum bid for =
each=20
itemname. The easiest first step it to actually determine what the =
highest=20
bid for each item actually is then use that information to build the =
rest=20
of what you wanted.=20

The most portable and easiest to write solution to this is the two-table=20=

method ( I think I divined your column names correctly, maybe not...):

# begin example #

CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid=20=

FROM PHPAUCTIONS_bids GROUP BY auction;

SELECT=20
        CONCAT(a.title, " (", LEFT(b.auction,4), ")") AS FullTitle
        , SUBSTRING_INDEX(u.name, " ", 1) AS fname
        , b.bid=20
FROM    tmpWinners AS w
INNER JOIN PHPAUCTION_bids AS b
        ON b.auction =3D w.auction
        AND b.bit =3D w.winningbid
INNER JOIN PHPAUCTION_users AS u
        ON b.bidder=3Du.id
INNER JOIN PHPAUCTION_auctions AS a=20
        ON b.auction=3Da.id
ORDER BY FullTitle=20
LIMIT 35;

DROP TEMPORARY TABLE tmpWinners;

# end example #

Does that make sense? You should be able to expand on that pattern to=20
build whatever list you want. I showed the full-chain of how each table=20
relates to another but you could have simplified the query above, can =
you=20
see where?


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine