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.
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.
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.
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.