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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
