I think the answer is much more simple ;
SELECT s.name, MAX(t.score) as topScore, t.day AS topScoreDay
FROM student as s
LEFT JOIN testScore AS t ON s.id = t.studentId
GROUP BY s.id
ORDER BY topScore DESC, t.score ASC;
Result :
name | topScore | topScoreDay |
---|---|---|
steve | 100 | 5 |
joe | 95 | 15 |