Mysql Largest Number By Group

I have messed around with this code for quite sometime. First thing I have my SQL table setup as char instead of decimal because I don't want the number to always show a decimal v

Solution 1:

In general ORDER BY in a sub-query makes no sense. (It only does when combined with FETCH FIRST/LIMIT/TOP etc.)

The solution is to use a correlated sub-query to find the heaviest fish for the "main query"'s current row's username, location, species combination. If it's a tie, both rows will be returned.

SELECT*FROM entries e1
WHERE username = :userANDCAST(weight ASDECIMAL(9,3)) = (selectmax(CAST(weight ASDECIMAL(9,3)))
                                      from entries e2
                                      where e1.username = e2.username
                                        and e1.location = e2.location
                                        and e1.species = e2.species)

Note that char for weight is still a bad choice, beacause of that you have to cast both sides when comparing values. Go back to decimal in your table!