SQL query to select maximum / highest value per group

Question

I have an existing SQL query that I need to add conditions to. I am testing this in SequelPro 1.0.2.

Here is what the existing query looks like:

SELECT distinct c.cue_id, c.*, cat.cat_name, s.style_name, t.tempo_name  
FROM cues c 
left join categories cat on c.cat_id = cat.cat_id 
left join styles s on c.style_id = s.style_id 
left join tempos t on c.tempo_id = t.tempo_id 
inner join master_cue_id m on m.cue_id = c.cue_id    
WHERE  ( (c.key_id_arry LIKE '%108500%' ) ) 
AND c.cue_status = 'Active' 
ORDER BY (
  CASE WHEN (key_id_arry LIKE '108500,%' OR key_id_arry LIKE '%,108500' OR key_id_arry LIKE '%,108500,%') 
THEN 1 ELSE 2 END), c.cue_rating DESC, c.cue_title, c.cue_trk_num ASC

here are the conditions I need to add to it:

There is a column on the cues table called cue_rating (INT 0-10). I need to return the SINGLE highest rated cue PER master_cue_id (there are about four or five cue_ids per master_cue_id).

If the cue_ratings are the same, I need to get the LOWEST cue_id.

Here is what I've tried so far.

SELECT distinct c.cue_id, c.*, cat.cat_name, s.style_name, t.tempo_name  
FROM cues c 
left join categories cat on c.cat_id = cat.cat_id 
left join styles s on c.style_id = s.style_id 
left join tempos t on c.tempo_id = t.tempo_id 
inner join master_cue_id m on m.cue_id = c.cue_id   
INNER JOIN 
  (SELECT MAX(c2.cue_rating) AS MaxCueRating, MIN(c2.cue_id) AS MinId, m2.master_cue_id 
  FROM cues c2 
  INNER JOIN master_cue_id m2 
  ON m2.cue_id = c2.cue_id 
  GROUP BY m2.master_cue_id) maxratedcues 
ON m.master_cue_id = maxratedcues.master_cue_id 
AND c.cue_rating = maxratedcues.MaxCueRating   
WHERE  ( (c.cue_desc LIKE '%futurebass%') ) and  c.cue_status = 'Active' 
GROUP BY m.master_cue_id 
ORDER BY (CASE WHEN (key_id_arry LIKE '108500,%' OR key_id_arry LIKE '%,108500' OR key_id_arry LIKE '%,108500,%') 
THEN 1 ELSE 2 END), c.cue_rating DESC, c.cue_title, c.cue_trk_num ASC

I expected to get the highest rated cue per each master_cue_id. The number of results was right, but I was not being returned the highest rated cues.

I've even tried simplifying the query to just return just the highest rated cues per master_cue_id without the keyword search queries:

SELECT * FROM cues
INNER JOIN master_cue_id m
ON cues.cue_id = m.cue_id
WHERE cue_rating = 
    (SELECT MAX(c.cue_rating) 
    from cues as c 
    INNER JOIN master_cue_id m2 
    ON m2.cue_id = c.cue_id 
    WHERE cues.cue_id = c.cue_id
    )
    GROUP BY m.master_cue_id

Ironically, it seems this was returning some of the highest rated cues (if they were all the same rating), but it would actually return the lowest rated cues.

EDIT: SQLFIDDLE