CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3224

Mysql query Max not working

What i want to happen is group by parentid first, then group by position, which i have done. In that group i want the name with the highest rating to be displayed, which isn't happening. Instead the lowest id for each group is being displayed. The results should be tv1,tv3,tv5,tv7; as these are the highest rated values for each group.

  id |  name| parentid|  position| rating  |
   1 |  tv1 |    1    |    1     |    6    |
   2 |  tv2 |    1    |    2     |    5    |
   3 |  tv3 |    1    |    2     |    7    |
   4 |  tv4 |    1    |    2     |    3    |
   5 |  tv5 |    5    |    1     |    8    |
   6 |  tv6 |    5    |    1     |    2    |
   7 |  tv7 |    3    |    1     |    9    |
   8 |  tv8 |    3    |    1     |    3    |



    $getquery = mysql_query("SELECT name,MAX(rating) FROM outcomes GROUP BY position,parentid") or die(mysql_error());

while($row=mysql_fetch_assoc($getquery)) {

$name = $row['name'];
$rating = $row['rating'];

echo "<p>Name: $name - $rating</p><p></p>";

}

Upvotes: 1

Views: 2047

Answers (1)

Ian Clelland
Ian Clelland

Reputation: 44112

It's not that the lowest id is being displayed -- you're not actually selecting the id column. Probably what you are seeing is the first entry in the name column for each group.

SELECT name, MAX(rating)

doesn't do what you think it does -- it doesn't instruct MySQL to pick the maximum value from the rating column, and also return the name that is associated with that row (aside: what do you think it would return if there was a tie for the maximum rating? What do you think it would return if you used AVERAGE rather than MAX?)

What it does instead is return the correctly calculated MAX(rating), and then one of the names out of that group. It doesn't guarantee which one gets returned, and it can change depending on how it decides to execute the query.

In fact, because of the undefined nature of a query such as this, it's not even legal SQL in other databases. (Try this in Postgres, and you'll get an error. Heck, try it in MySQL with the ONLY_FULL_GROUP_BY option enabled, and you'll get a similar error)

If what you want to do is find the maximum rating for each group, and then find the name associated with it, you'll have to do something like this:

SELECT name, max_rating FROM outcomes
JOIN (SELECT position, parentid, MAX(rating) AS max_rating from outcomes group by position, parentid) AS aggregated_table
USING (position, parentid)
WHERE rating = max_rating

(There are four or five other ways to do this, searching this site for mysql and aggregation will likely turn them up)

Upvotes: 4

Related Questions