MySQL add column with numeric category taking an increment based on another column value and position

Question

I have a table in MySQL as described below

rownum,value,status
1,16,1
2,32,1
3,16,1
4,23,0
5,33,0
6,16,0
7,22,0
8,13,1
9,43,1
10,32,1
11,45,0
12,28,0
13,23,0
14,28,0
15,31,1
16,13,1
17,44,1

Here the third column shows the status of the row basis which I want to add a new column. The logic required is for every set of repeated values of 1 in column "status" the new column will get a number assigned to all of those rows. And this number will increment with +1 when the next set of 1s are found. the output table should look as below.

rownum,value,status,category
1,16,1,1
2,32,1,1
3,16,1,1
4,23,0,null
5,33,0,null
6,16,0,null
7,22,0,null
8,13,1,2
9,43,1,2
10,32,1,2
11,45,0,null
12,28,0,null
13,23,0,null
14,28,0,null
15,31,1,3
16,13,1,3
17,44,1,3

I am a bit confused on how this logic can be created and any inputs will be really helpful. Thanks!

Answer 1

You can achieve this in MySQL using User-defined Session variables, and using conditional functions like If(). We store previous row's status value in @stat variable, and compare it with current row's status value, to get results as intended.

But if it is just for display purposes, you should seriously consider handing this in your application code.

Nevertheless, try the following query (DB Fiddle DEMO):

SELECT 
  IF( dt.status = 0, 
      NULL, 
      IF( @stat = 0, @cat := @cat + 1, @cat )
    ) AS category, 
  dt.rownum, 
  dt.value, 
  @stat := dt.status AS status 
FROM 
(
 SELECT 
   rownum, 
   value, 
   status 
 FROM your_table
 ORDER BY rownum ASC 
) AS dt 
CROSS JOIN (SELECT @cat := 0, 
                   @stat := 0) AS init_user_vars