title: MS Access Max and Select top n query issue with duplicate values
body: <p>This is my very first post, so hopefully I did not break any rules.</p>
  
  <p>I am an MS Access newbie and my sql knowledge is limited at best.  I am trying to write a query that will return the top 3 test scores for a given student.  My tables looks like the following:</p>
  
  <pre>
  Student ID    Test ID      Score
  1                1           95
  1                2           90
  1                3           90
  1                4           90
  2                1           99
  2                2           95
  2                3           90
  2                4           90
  </pre>
  
  <p>Through research on this forum, I've been able to find some very helpful code that basically does what I'm looking for EXCEPT when I have multiple test scores that are the same value.  In that situation, I get more than the top 3 scores.  So in the case of student #1, the query will return all 4 test scores, not just the first 3.  I tried adding a unique index field to order by but had no luck.  So far, my code is as follows:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT MyTable.StudentID, MyTable.TestID, Max(MyTable.TestScore) AS MaxOfTestScore
  FROM MyTable
  GROUP BY MyTable.StudentID, MyTable.TestID, MyTable.TestScore
  HAVING MyTable.TestScore In (SELECT TOP 3 TestScore FROM MyTable T2 WHERE 
  MyTable.StudentID = T2.StudentID ORDER BY TestScore DESC)
  ORDER BY MyTable.StudentID, MyTable.TestScore DESC;
  </code></pre>
  
  <p>Credit goes to Sarkazein for providing this code so far.  I tried to comment on that thread but couldn't since I don't have any reputation points.</p>