Get MAX value from one column and MIN from another column

mysql

Solution

Your attempt wasn't that far off. You were missing a necessary part of the first JOIN ... ON clause though, and the second JOIN isn't necessary.

SELECT tbl1.stage_level_id, tbl1.max_value, MIN(s.moves) AS moves
FROM 
(
  SELECT stage_level_id, MAX(value) AS max_value
  FROM scores
  GROUP BY stage_level_id
) tbl1
LEFT JOIN scores s ON tbl1.stage_level_id = s.stage_level_id AND tbl1.max_value = s.value
GROUP BY stage_level_id

DEMO

Problem

I've been building a game based on Candy Crush. The Score table has the three following columns: ``` stage_level_id | value | moves ------------------------------------------------------ 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 350 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 500 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 13500 | 100 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 7500 | 25 ``` I need to get the top Score grouped by `stage_level_id`. If an `stage_level_id` have the same `Value` (as the one ending with 53), it must return the row with the smallest number of `Moves`. I'm trying the following but it's not working as expected: ``` SELECT a.stage_level_id, MAX(a.value) as max_value, a.moves FROM scores a LEFT JOIN scores b ON ( a.stage_level_id = b.stage_level_id ) RIGHT JOIN scores c ON ( c.moves = ( SELECT MIN(moves) as moves FROM scores WHERE c.stage_level_id = a.stage_level_id ) ) WHERE a.player_id = 1475332386040815 GROUP BY a.stage_level_id ``` The expected result is: ``` stage_level_id | value | moves ------------------------------------------------------ 9f7678f0-fc8f-11e3-a398-b2227cce2b53 | 35000 | 350 9f7678f0-fc8f-11e3-a398-b2227cce2b54 | 15000 | 125 9f7678f0-fc8f-11e3-a398-b2227cce2b55 | 12500 | 350 ``` What I'm doing wrong?

Original source