Get Incremental number using partition by In mysql

Question

I have Some unique scenario

Input:

   VAR
1   A
2   A
3   A
4   NULL
5   NULL
6   A
7   A
8   A
9   B
10  B
11  B
12  NULL
13  B
14  B
15  B
16  C
17  C
18  C
19  C

I have input data in above order. I need to create output column as Rank using partition column VAR. But if there is null in between it Rank should get reset its counter.

Expected Output:

   VAR  output
1   A   1
2   A   2
3   A   3
4   NULL    
5   NULL    
6   A   1
7   A   2
8   A   3
9   B   1
10  B   2
11  B   3
12  NULL    
13  B   1
14  B   2
15  B   3
16  C   1
17  C   2
18  C   3
19  C   4

Answer 1

This looks like a gaps and islands problem. Assuming that you have another column that defines the ordering of the records (say, id), here is an approach that uses the difference between row numbers to define the groups of adjacent records:

select 
    var,
    case when var is not null
        then row_number() over(partition by var, rn1 - rn2 order by id) 
    end rnk 
from (
    select 
        t.*,
        row_number() over(order by id) rn1,
        row_number() over(partition by var order by id) rn2
    from mytable t
) t
order by id 

Note that window functions are available in latest MySQL version only (that is, version 8.0).