MySQL Update Query – Reset Counter When Date Column Changes

MySQL

column1   column_sec attempt      date    counter
1250       1250_A     1        16-06-2016    1     
1250       1250_B     1        16-06-2016    1  
1250       1250_C     1        16-06-2016    1  
1250       1250_A     2        16-06-2016    2  
1250       1250_B     2        16-06-2016    2  
1250       1250_C     2        16-06-2016    2  
1250       1250_A     3        06-06-2016    1  
1250       1250_B     3        06-06-2016    1  
1250       1250_C     3        06-06-2016    1  
1250       1250_A     4        06-06-2016    2  
1250       1250_B     4        06-06-2016    2  
1250       1250_C     4        06-06-2016    2

I want to write an update query when my date changes it will reset the counter column as shown above,
Can anyone help ?

So far i have tried this, But it increments counter by 1 till the end of the table.It did not help.

set @v1 := '', @v2 :='',@v3 :='', @num := 1;

select column1,column_sec,attempt,date,
   @num := if(@v1 := date,if(@v2 := column_sec,if(@v3 := column1,@num + 1,1),1),1) as counter
from table_name where date is not null
order by column1,column_sec,attempt;

Best Answer

Do it like this:

select column1,column_sec,attempt,date,
@num := if(@v1 != date OR @v2 != column_sec OR @v3 != column1, 1, @num + 1) AS counter,
@v1 := date,
@v2 := column_sec,
@v3 := column1
from table_name 
cross join (select @v1 := '', @v2 :='',@v3 :='', @num := 0) var_init_subquery
where date is not null
order by column1,column_sec,attempt;

The key is, that you have to check first, then assign the value of the current row. In the query you tried, you never assigned anything to the variables. When the next row (and the therefore the select clause) is processed, the variables hold the value of the previous row in the check, then the value of the current row is assigned to the variables.

Related Question