...

            // Mark the latest attempt for every submission in mod_assign.

            $maxattemptsql = 'SELECT assignment, userid, groupid, max(attemptnumber) AS maxattempt

                                FROM {assign_submission}

                            GROUP BY assignment, groupid, userid';

            // Note: souterouter looks redundant below, but it forces

            // MySQL to use an in memory table to store the results of the

            // inner query. Without this MySQL would complain that the UPDATE

            // is operating on the same table as the FROM (which is true).

            $maxattemptidssql = 'SELECT souterouter.id FROM (

                                    SELECT souter.id

                                       FROM {assign_submission} souter

                                       JOIN (' . $maxattemptsql . ') sinner

                                         ON souter.assignment = sinner.assignment

                                        AND souter.userid = sinner.userid

                                        AND souter.groupid = sinner.groupid

                                        AND souter.attemptnumber = sinner.maxattempt

                                ) souterouter';

            $select = 'id IN(' . $maxattemptidssql . ')';

            $DB->set_field_select('assign_submission', 'latest', 1, $select);

...

# Query_time: 5291.641908 Lock_time: 0.000134 Rows_sent: 0 Rows_examined: 118023849031

SET timestamp=1440071732;

UPDATE mdl_assign_submission SET latest = '1' WHERE id IN(SELECT souterouter.id FROM (

SELECT souter.id

FROM mdl_assign_submission souter

JOIN (SELECT assignment, userid, groupid, max(attemptnumber) AS maxattempt

FROM mdl_assign_submission

GROUP BY assignment, groupid, userid) sinner

ON souter.assignment = sinner.assignment

AND souter.userid = sinner.userid

AND souter.groupid = sinner.groupid

AND souter.attemptnumber = sinner.maxattempt

) souterouter);