Ok I tried the following SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT MAX(bid_amount) FROM bids WHERE auto_dealer_id='3'); which gives +-----------+---------+------------+ | dealer_id | auto_id | bid_amount | +-----------+---------+------------+ | 3 | 12 | 90000 | +-----------+---------+------------+ 1 row in set (0.00 sec)
wich is the max bid overall, what I want is the max bid for each auto_id ... How would I go about this ? On 3/17/06, Michael Stassen <[EMAIL PROTECTED]> wrote: > > Gregory Machin wrote: > > Hi. > > I have the following table > > > > | bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp > | > > Bid_Status | > > > +---------+-------------+----------------------+------------+-----------------+----------------+-----------------+ > > | 1 | 3 | 3 | 12 | > 20000 > > | NULL | 1 | > > | 2 | 3 | 3 | 12 | > 30000 > > | NULL | 1 | > > | 3 | 24 | 3 | 12 | > 40000 > > | NULL | 1 | > > | 4 | 24 | 3 | 12 | > 50000 > > | NULL | 1 | > > | 5 | 24 | 3 | 12 | > 60000 > > | NULL | 1 | > > | 6 | 3 | 24 | 14 | > 40000 > > | NULL | 1 | > > | 7 | 3 | 3 | 13 | > 40000 > > | NULL | 1 | > > | 8 | 24 | 3 | 12 > | 70000 > > | NULL | 1 | > > | 9 | 24 | 3 | 13 | > 59000 > > | NULL | 1 | > > | 10 | 24 | 3 | 12 | > 80000 > > | NULL | 1 | > > | 11 | 24 | 3 | 13 | > 60000 > > | NULL | 1 | > > > > > > where auto_dealer_id is the dealer who put the car on auction , > > auto_id is the id of the car on auction, > > bid_amount is the amount did on the car but dealer_id > > > > > > what I want is to get the max bid placed for each car and the dealer_id > who > > placed it. > > I currnetly have the following: > > mysql> SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS > 'bid_amount' > > FROM bids WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY > auto_id; > > +-----------+---------+--------+------------+ > > | dealer_id | auto_id | bid_id | bid_amount | > > +-----------+---------+--------+------------+ > > | 3 | 12 | 1 | 80000 | > > | 3 | 13 | 7 | 60000 | > > +-----------+---------+--------+------------+ > > > > But this output is wrong because if you refer to the source table above > the > > dealer_id should be 24 in both cases.. > > What have I missed ? > > > > Many Thanks > > Gregory Machin > > GROUP BY does not return rows. It returns group names and aggregate > stats. You > are grouping on auto_id, so you get random (actually, the first found) > values > for dealer_id and bid_id for each group. Many systems wouldn't even allow > this > query. Mysql does allow you to select columns not present in the GROUP BY > clause as a convenience, but you are warned only to use columns with > unique > values per group > <http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html>. > > Yours is a FAQ, however, with 3 solutions in the manual > < > http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html > >. > > Michael > -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
