Get MAX value from one column and MIN from another column

Question

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?

Answer 1

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