SQL for returning only the latest quiz attempts

Stephen Ladek

• January 18, 2023 18:23

Credit (Toni): I'm trying to produce some SQL for returning only the latest quiz attempts from mdl_quiz_attempts. My issue is that a student may attempt a quiz 3 times in quick succession and I only want to capture the latest attempt (and only if it has not been marked). as anyone tackled this successfully? Thanks in anticipation.

1

• 

  Jason Rodgers

  • June 29, 2023 11:58
  • Edited

  Possibly something like this.  You could remove the WHERE condition for the course id to go system wide.

  ------

  SELECT 
  MQ.course, 
  MQ.name as QuizName,
  Concat('https://yourURL/mod/quiz/review.php?attempt=',MQA.id) AS link, 
  MQA.userid, 
  MAX(MQA.attempt) AS attempt 

  FROM mdl_quiz_attempts MQA
  INNER JOIN mdl_quiz MQ ON MQA.quiz=MQ.id
  LEFT JOIN mdl_quiz_grades QG ON QG.quiz=MQ.id
  WHERE MQ.course=9468 and QG.grade is NULL
  GROUP BY MQA.userid, MQA.quiz

  1

Please sign in to leave a comment.