Re: SELECT SUM() with only top 4 results

[Peter Brawley]

Unfortunately correlated subqueries like yours introduce referencing problems of the sort you tripped over, and often perform badly.

If you have MySQL 8, the windowing functions Row_Number() Over... and Rank() give an elegant solution eg ...

SELECT tray_id, score, `rank`
FROM ( 
  SELECT 
    tray_id, score, 
    ROW_NUMBER() OVER( 
      PARTITION BY tray_id 
      ORDER BY tray_id,score 
    ) AS `rank`
  FROM scores
  ORDER BY tray_id
) AS tmp 
WHERE tmp.`rank` <= 4   -- TOP 4
ORDER BY tray_id, score;

Note `rank` needs backticks round it since presence of the Rank() function makes it a reserved word since 8.0.

The query's explained under "Within-group quotas (Top N per group)" at https://www.artfulsoftware.com/queries.php, as are top-N queries for pre-8.