Create Column in sql based on condition?

Question

table:

  Student_Id    Roll_No    Date   _Rank
1    101         10101    201905    3
2    101         10101    201910    6
3    101         10101    201912    9
4    101         10102    201905    15
5    101         10102    201910    9
6    101         10102    201912    3
7    103         10103    201905    3
8    103         10103    201910    9
9    103         10103    201912    9
10   103         10104    201905    3
11   103         10104    201910    9
12   103         10104    201912    9

i need to create a result column based on the condition.

1. check for particular Student_Id,Roll_No if rank is 9 and rank> 9 has not appeared before 9 as per date then 1 else 0.

2. if some Student_Id,Roll_No has multiple 9 then flag only the first occurrence.

Result:

  Student_Id    Roll_No    Date   _Rank   Result
1    101         10101    201905    3       0
2    101         10101    201910    6       0
3    101         10101    201912    9       1 
4    101         10102    201905    15      0 ---
5    101         10102    201910    9       0 --- 15 > 9, came before 9 for that Roll_No
6    101         10102    201912    3       0
7    103         10105    201905    9       1
8    103         10103    201910    9       1
9    103         10103    201912    9       0
10   103         10104    201905    3       0
11   103         10104    201910    9       1 --- first occurrence should 1
12   103         10104    201912    9       0

Query:

SELECT Student_Id, Roll_Number, Date, _Rank,
ROW_NUMBER() OVER( PARTITION BY Roll_Number ORDER BY Date) as rw_number
FROM table t
-- this is giving the row_number which is appeared first.

Answer 1

I think you want:

select t.*, 
       (case when _rank = 9 and 
                  row_number() over (partition by roll_no, _rank order by date) = 1 and
                  max(_rank) over (partition by roll_no order by date) <= 9
             then 1 else 0
        end) as result
from t
order by roll_no, date;

The max(_rank) condition checks that no rank higher than 9 has occurred up to a given row.

Here is a db<>fiddle.

Answer 2

You can use window functions:

select 
    t.*, 
    case 
        when _rank = 9 and row_number() over(partition by roll_no, _rank order by date) = 1
        then 1
        else 0
    end result
from mytable t