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?

There is no current attempt. For Sam the last attempt was attempt 3 and for Mat the last attempt was attempt 2. N number of attempts can be taken by each,even if they pass. — twitter investor, Jun 29 '21 at 06:37
I believe, What is your current attempt? = What have you done till now? — ankush__, Jun 29 '21 at 06:39

score 3 · Accepted Answer · answered Jun 29 '21 at 06:40

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

score 2 · Answer 2 · answered Jun 29 '21 at 11:21

You could also use aggregation for this:

select stuid, stuname, 
       max(exam) keep (dense_rank first order by attempt) as exam,
       min(attempt),
       max(score) keep (dense_rank first order by attempt) as score
from exams
where score >= 60;

The keep syntax is Oracle's (fancy? verbose?) way of implementing a "first" aggregation function.

Need to get first successful completion date from completions

2 Answers2