On Friday 17 August 2001 07:13, Jim Lynn wrote:
> I have a database of bids on auctions that I keep separate from the auction
> information. The problem comes when I attempt to generate a list of
> auctions with highest bid information. I set the database for bids like
> this:
>
> ANum, varchar(20), the auction number
> Bidders, int(4), number of bidders at time of this bid
> Bidder, varchar(50), username of current bidder
> CPrice, decimal(9,2), current bid
>
> Typical records:
> 12345 1 bidder1 20.00
> 12345 2 bidder2 25.00
> 12345 3 bidder3 30.00
>
> I tried: select ANum, max(Bidders), Bidder, CPrice from Bids group by ANum
> and it returns:
>
> 12345 3 bidder1 20.00
>
> which gives me the higest bid number, but the information from the first
> bid in the database. How can I retrieve the records that reflect the
> highest bids for each auction???
AFAIK this can only be done in a single SQL statement with a subselect, eg:
SELECT *
FROM bids
WHERE Bidder IN (SELECT MAX(Bidders)
FROM bids
GROUP BY ANum)
Unfortunately MySQL does not yet support subselect (see:
http://www.mysql.com/doc/M/i/Missing_Sub-selects.html ), so the only way
round this I can see would be on the application side, e.g.
SELECT MAX(Bidders), Bidder
FROM bids
GROUP BY ANum
then step through the result set selecting the row for each auction
individually using Bidder as the selection criterium.
HTH in some way.
(If there is a more elegant solution to this problem in MySQL I'd love to
hear it)
Ian Barwick
--
Ian Barwick - Developer - [EMAIL PROTECTED]
akademie.de asp GmbH - http://www.akademie.de
"To query tables in a MySQL database is more fun than eating spam"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
