title: SQL Query: Attempting to pull the most recent with other parameters
body: <p>I work in a school system with student data in a database.  At least each year (and sometimes multiple times a year) a new row of data is created for that student which represents a new "document".  I am trying to write a query to pull the most recent row of information for that student if that row meets other criteria (e.g. it needs to be of a type 'compsped' and it needs to have a status of 'F' or 'I').  When I run the query I wrote below, it works pretty well, but it seems to be missing some data.  I think the reason it is missing some data is because it is looking first for the most recent document and THEN it is filtering out the documents that don't meet the other criteria.  Instead, I want it to first filter out the documents that don't meet the other criteria and of THAT list I want it to pull the most recent row from that list.  We are using SQL Server 20016.  Hopefully that makes sense.  Please ask questions if it doesn't.  Thanks!</p>
  
  <pre><code>SELECT evaluationreports1.Status, 
  evaluationreports1.EvalDueDate, 
  evaluationreports1.EvalRptDate, 
  evaluationreports1.StudentID, 
  evaluationreports1.TypeAbbrev
  FROM PlansAnoka.dbo.evaluationreports evaluationreports1
  WHERE evalrptdate = (select max(evalrptdate) from evaluationreports where studentid = evaluationreports1.studentid) 
  AND (evaluationreports1.TypeAbbrev='CompSpEd') 
  AND (evaluationreports1.Status In ('F','I'))
  </code></pre>