Need to get first successful completion date from completions

Question

My data looks like this:

StuId StuName Exam Attempt Score
1       Sam   Eng  1       45
1       Sam   Eng  2       58
1       Sam   Eng  3       63
2       Mat   Eng  1       65
2       Mat   Eng  2       75

The student has passed if he has gotten above 60. I would want to fetch first successful attempt for each student and ignore all other attempts My expected result set looks like this:

StuId   StuName Exam Attempt Score
       
     1       Sam   Eng  3       63
     2       Mat   Eng  1       65

Is there a way I can achieve this in SQL Server?

Answer 1

You could use a where clause to filter out the unsuccessful attempts, and then use row_number to assign an identifier to each attempt, per student, and pick the first one:

SELECT StuId, StuName, Exam, Attempt, Score
FROM   (SELECT StuId, StuName, Exam, Attempt, Score,
               ROW_NUMBER() OVER (PARTITION BY StuId, Exam ORDER BY Attempt) AS rn
        FROM   exams
        WHERE  Score >= 60) t
WHERE  rn = 1