MySQL query results on MAX() and GROUP BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

MySQL query results on MAX() and GROUP BY

Post 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?
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post 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?
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Post 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 :?
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post 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.. :)
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Post by nutkenz »

yes, they all have a high_bid value
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Post by nutkenz »

still haven't found a solution :(
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Post by nutkenz »

Error performing query: This version of MySQL doesn't yet support 'ROLLUP'
Post Reply