Get data from records having multiple count and manipulate them

Question

I have a sample record like below in one of my SQL tables:

What I want is my select query should take the student record which has multiple count of same subjects, ie, Student A has a count(6) for subject COD and use a case statement to give the first record as failed and rest of the record as After Fail.

Ex output:

I'm not able to implement a correct logic to get this.

Thanks in advance!

Answer 1

You can use window functions. The logic is to enumerate and count the number of records having the same student and subject in a subquery, then use that information in the outer query:

select id, student, subject, marks,
    case 
        when cnt > 1 and rn = 1 then 'Fail' 
        when cnt > 1 and rn > 1 then 'After fail'
    end output
from (
    select t.*, 
        row_number() over(partition by student, subject order by id) rn,
        count(*) over(partition by student, subject) cnt
    from mytable t
) t