Page 1 of 1

MySQL query results on MAX() and GROUP BY

Posted: Fri Jul 22, 2005 6:58 pm
by nutkenz
When I do this query from PHP:
$result = mysql_query("SELECT armor_key, MAX(high_bid), value_full FROM armor WHERE armor_status='2' GROUP BY high_bid DESC LIMIT 3");

I don't seem to get the data for high_bid itself... It's probably a simple solution; how do I specify that I also want to get the high_bid value? I've tried removing and replacing the GROUP BY, but when I don't use it, I get a MySQL error, and when I enter another column like value_full, I get unwanted results... Can anyone help?

Posted: Fri Jul 22, 2005 7:48 pm
by dreamline
$result = mysql_query("SELECT armor_key, high_bid, MAX(high_bid), value_full FROM armor WHERE armor_status='2' GROUP BY high_bid, armor_key DESC LIMIT 3");

Is this what you mean?

Posted: Sat Jul 23, 2005 4:16 am
by nutkenz
The high_bid still isn't being listed...


while ( $row = mysql_fetch_array($result) ) {
$high_bid = $row["high_bid"];


This works for all the other fields :?

Posted: Sat Jul 23, 2005 4:31 am
by dreamline
Are you sure that you have a value in high_bid with armor_status='2'? If so then we need sum more code.. :)

Posted: Sat Jul 23, 2005 5:22 am
by nutkenz
yes, they all have a high_bid value

Posted: Sun Jul 24, 2005 6:06 am
by nutkenz
still haven't found a solution :(

Posted: Sun Jul 24, 2005 7:09 pm
by timvw
Afaik you can only have columns in your select clause if they are in the group by clause (or if they are in an aggregate function)

Btw, if you group by high_bid you will end up with rows that have high_bid = MAX(high_bid)...

Meaby you want to use the "WITH ROLLUP" option.. (More info http://dev.mysql.com/doc/mysql/en/group ... fiers.html)

Code: Select all

SELECT high_bid, MAX(high_bid) AS max_bid, armor_key, value_full 
FROM armor 
GROUP BY high_bid, armor_key, value_full

Posted: Mon Jul 25, 2005 3:13 am
by nutkenz
Error performing query: This version of MySQL doesn't yet support 'ROLLUP'