[Solved] mySQL Largest number by group

by


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 = :user
  AND CAST(weight AS DECIMAL(9,3)) = (select max(CAST(weight AS DECIMAL(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!

2

solved mySQL Largest number by group