Rewrite SQL query to Fix Functional Dependency Issue Caused By MySQL 5.7 Strict Mode

I recently upgraded my MySQL server to version 5.7 and the following example query does not work:

SELECT * 
FROM (SELECT * 
        FROM exam_results 
        WHERE exam_body_id = 6674 
        AND exam_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) 
        AND subject_ids LIKE '%4674%' 
        ORDER BY score DESC 
    ) AS top_scores 
GROUP BY user_id 
ORDER BY percent_score DESC, time_advantage DESC 
LIMIT 10

The query is supposed to select exam results from the specified table matching the top scorers who wrote a particular exam within some time interval. The reason why I had to include a GROUP BY clause when I first wrote the query was to eliminate duplicate users, i.e. users who have more than one top score from writing the exam within the same time period. Without eliminating duplicate user IDs, a query for the top 10 high scorers could return exam results from the same person.

My question is: how do I rewrite this query to remove the error associated with MySQL 5.7 strict mode enforced on GROUP BY clauses while still retaining the functionality I want?

That is because you never really wanted aggregation to begin with. So, you used a MySQL extension that allowed your syntax -- even though it is wrong by the definition of SQL: The GROUP BY and SELECT clauses are incompatible.

You appear to want the row with the maximum score for each user meeting the filtering conditions. A much better approach is to use window functions:

SELECT er.* 
FROM (SELECT er.*,
             ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score DESC) as seqnum
      FROM exam_results er 
      WHERE exam_body_id = 6674 AND
            exam_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND
            subject_ids LIKE '%4674%' 
    ) er
WHERE seqnum = 1
ORDER BY percent_score DESC, time_advantage DESC 
LIMIT 10;

You can do something similar in older versions of MySQL. Probably the closest method uses variables:

SELECT er.*,
       (@rn := if(@u = user_id, @rn + 1,
                  if(@u := user_id, 1, 1)
                 )
       ) as rn
FROM (SELECT er.*
      FROM exam_results 
      WHERE exam_body_id = 6674 AND
            exam_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND
            subject_ids LIKE '%4674%' 
      ORDER BY user_id, score DESC
     ) er CROSS JOIN
     (SELECT @u := -1, @rn := 0) params
HAVING rn = 1
ORDER BY percent_score DESC, time_advantage DESC 
LIMIT 10