MySQL Max grouping wrongly the data

mysql database
mysql database

I was trying to build a mysql image gallery, and for that reason i wanted to retrieve the largest value, according to a specific column, which would be smaller than a url variable i would retrieve from the user interface. This is the MySql command i used initially and which retrieved wrong results:

select max(idpacks), packuuid FROM table  where idpacks<"60" ;

which returned the following results:

# max(idpacks), packsuuid
'59', '0e1fdf98b90011e48438d067e54001ba'

The problem:

Even though the value 59 in the above result is the correct one, the packuuid does not correspond to the same row. Digging in a bit i found that it actually corresponds to the row which has the smallest idpacks value.  This took me all day to figure out 😀

The cause:

Quoting from web:

MySQL actually permits that an aggregate query returns columns not used in the aggregation (i.e. not listed in GROUP BY clause). It could be considered as flexibility, but in practice this can easily lead to mistakes if a person that designs queries does not understand how they will be executed

So I needed to modify the query.  My SQL query skills are not the best so bare with me. This is the solution I found to get the values i needed to the same row:

SELECT idpacks,packsuuid FROM packs  where idpacks=(select max(idpacks) from packs  where idpacks<"60" );

Which returned the correct values (correct packsuuid for idpacks 59):

# idpacks, packsuuid
'59', '0f6914f0b90011e48438d067e54001ba'

Worked for me!

Leave a Reply