title: Selecting or Updating with 2 group criteria for a max value on mysql
body: <p>I'm trying to figure out how to use group and max() on joins correctly, I'm doing some parsing of a moodle(Open source school software) mysql database. Students are allowed to retake the quizes indefinatly for this particular program, but I need to be able to update the course completion date to reflect the last time they took the test because a lot of other things depend on the completion fields. 
   The mdl_quiz_attempts table stores all attempts for all quizes, the userid will have many of the same entries, but the attempt number is not unique to the table, but instead unique to both the student AND the key for the row. Meaning students have multiple entries. On the mdl_course_modules table, The instance field is the key for the mdl_quiz table, and the mdl_course_modules_completion coursemoduleid field is the key for mdl_course_modules. </p>
  
  <p>So what I want to do is this:
  given a student id
  UPDATE mdl_course_completion.timemodified to mdl_quize_attempts.timemodified
  WHERE the row on mdl_quiz_attempts is the max attempt by userid for each quiz.(the quiz field on the quiz_attempts has to be looked up through in course module instance table to get the, instance id for course completion module id)</p>
  
  <p>Here are example partial tables.</p>
  
  <pre><code>mdl_quiz_attempts
  id  quiz    userid  attempt timemodified    
  2   1   3   6   1365408901  
  6   1   4   1   1369873688  
  7   2   4   1   1369877532  
  8   7   4   1   1369881431  
  9   7   4   2   1369882897  
  12  5   4   1   1505165504  
  13  6   4   1   1369887643  
  17  8   4   1   1369958105  
  18  1   4   2   1374557701  
  22  7   4   3   1374639901  
  23  6   4   7   1374640202  
  24  5   4   2   1374639901  
  25  8   4   2   1374639901  
  26  2   4   2   1374639301  
  27  2   6   1   1376620469  
  29  2   12  1   1389915486  
  30  1   23  1   1390978667  
  31  1   23  2   1391030924  
  32  2   23  1   1392113103  
  33  2   23  2   1392696602  
  34  2   23  3   1392767435  
  35  7   12  1   1398914256  
  36  8   43  1   1405281193  
  37  1   50  1   1405522411  
  38  5   43  1   1505165504  
  
  mdl_course_modules
  id  course  module  instance    section 
  3   2   9   2   3   
  5   2   17  2   4   
  7   2   17  3   5   
  8   2   17  4   6   
  9   2   17  5   7   
  10  2   17  6   8   
  11  2   17  7   9   
  12  2   17  8   10  
  13  2   17  9   11  
  14  2   17  10  12  
  15  2   17  11  13  
  25  2   16  1   14  
  26  2   23  1   4   
  28  2   7   1   14  
  30  4   9   4   26  
  42  4   23  3   33  
  45  4   23  6   38  
  46  4   23  7   37  
  47  4   23  8   36  
  48  4   23  9   35  
  49  4   23  10  32  
  50  4   23  11  34  
  51  5   9   5   27  
  53  5   23  12  43  
  55  5   23  13  44  
  
  mdl_quiz
  id  name    
  10  Unit 10 Quiz    
  11  Unit 2 Quiz 
  12  Unit 3 Quiz 
  13  Unit 5 Quiz 
  14  Unit 1 Quiz 
  15  Unit 8 Quiz 
  16  Unit 9 Quiz 
  17  Unit 7 Quiz 
  18  Unit 4 Quiz 
  
  
  mdl_course_modules_completion
  id  coursemoduleid  userid  completionstate viewed  timemodified    
  14  25  2   0   1   0   
  15  25  6   0   1   0   
  67  25  4   1   1   1369873688  
  68  28  4   1   0   1369874483  
  69  192 4   1   0   1369875233  
  70  184 4   1   1   1369877532  
  </code></pre>