title: Efficiently pull different columns using a common correlated subquery
body: <p>I need to pull multiple columns from a subquery which also requires a WHERE filter referencing columns of the FROM table. I have a couple of questions about this:</p>
  
  <ol>
  <li>Is there another solution to this problem besides mine below?</li>
  <li>Is another solution even necessary or is this solution efficient enough?</li>
  </ol>
  
  <p><em>Example:</em></p>
  
  <p>In the following example I'm writing a view to present test scores, particularly to discover failures that may need to be addressed or retaken.</p>
  
  <p>I cannot simply use JOIN because I need to filter my actual subquery first (notice I'm getting TOP 1 for the "examinee", sorted either by score or date descending)</p>
  
  <p>My goal is to avoid writing (and executing) essentially the same subquery repeatedly.  </p>
  
  <pre><code>SELECT ExamineeID, LastName, FirstName, Email,
     (SELECT COUNT(examineeTestID)
      FROM exam.ExamineeTest tests
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2) Attempts,
     (SELECT TOP 1 ExamineeTestID
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY Score DESC) bestExamineeTestID,
     (SELECT TOP 1 Score
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY Score DESC) bestScore,
     (SELECT TOP 1 DateDue
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY Score DESC) bestDateDue,
     (SELECT TOP 1 TimeCommitted
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY Score DESC) bestTimeCommitted,
     (SELECT TOP 1 ExamineeTestID
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY DateDue DESC) currentExamineeTestID,
     (SELECT TOP 1 Score
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY DateDue DESC) currentScore,
     (SELECT TOP 1 DateDue
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY DateDue DESC) currentDateDue,
     (SELECT TOP 1 TimeCommitted
      FROM exam.ExamineeTest T
      WHERE E.ExamineeID = ExamineeID AND TestRevisionID = 3 AND TestID = 2
      ORDER BY DateDue DESC) currentTimeCommitted
  FROM exam.Examinee E
  </code></pre>