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'
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 😀
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!