Why is sql MAX function not working properly?

max, php, sql

Solution

This is not how aggregates like `MAX` work in SQL. Your confusion is coming from MySQL's (default) non-ANSI handling of aggregates.

Aggregates like `MAX` operate over groups. In the absence of a `group by` clause, the entire result set is considered to be a single group. Only expressions that are part of a `group by` clause can be included in a `select` clause without being enclosed in an aggregate. In the case where there is no `group by`, then all columns or expressions in the `select` clause must be contained in an aggregate.

However, MySQL's default configuration breaks this by allowing you to include non-grouped expressions in the `select` clause, but the row that any given expression uses to obtain its value is undefined; it could be any row within the group.

After that long-winded answer, if what you want to get is the maximum `rating` and the associated `content` column from the table for a given question, you can just do this:

select 
    rating, 
    content 

from answers 

where questionID = '$questionID' 

order by rating desc 

limit 1;

Problem

I have this query working to some extent. It returns the correct value for 'rating' (which output as 7, the highest rating), but the output for 'content' is from a different row in the table. (not the row of the highest rating, which is 7) ``` $bestAnswerQuery = MYSQL_QUERY("SELECT content, MAX(rating) as rating FROM answers WHERE questionID = '$questionID'"); $fetchBestAnswer = MYSQL_FETCH_ASSOC($bestAnswerQuery); echo "$fetchBestAnswer[content] $fetchBestAnswer[rating]"; ``` Can anyone tell me why? I've searched and cannot find out why this isn't working properly.

Original source