Search code examples
mysqlpartitionrow-number

Mysql below v8.0 assigning row_number (partition by) with @num

My MySQL version is below v8.0 and I would like to assign row_number() over (partition by AAB) with the table below. I tried the following codes and it did not work.How can I get it work?

select 
@part:=AAB as AAB,
AAG,AAD,
@num:=if(@part=AAB,@num:=@num+1,1) as rank
from IMPO_MEMB_AAAE a, (select @part:=null,@num:=0) b
order by AAB,AAG;

Table to rank

Solution

  • This problem highlights the issue with using variables for this form of computation. Your issue is that you are evaluating @num later in the SELECT list than you evaluate @part and as it happens @part gets assigned its new value before @num is computed, meaning that @part=AAB is always true. You can work around this by placing @num before @part in the SELECT list, but as they say in the manual:

    The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

    As it happens it generally does seem to work that way so until you upgrade you can probably use this query:

    select 
@num:=if(@part=AAB,@num:=@num+1,1) as rank,
@part:=AAB as AAB,
AAG,AAD
from IMPO_MEMB_AAAE a, (select @part:=null,@num:=0) b
order by AAB,AAG;

    Output:

    rank    AAB         AAG                     AAD
1       10000751    2020-01-02 16:06:00     41
1       10033980    2020-01-02 22:55:00     40
1       10041030    2020-01-02 22:50:00     41
2       10041030    2020-01-02 23:53:00     40
3       10041030    2020-01-02 23:53:00     40
4       10041030    2020-01-02 23:53:00     40
1       10049083    2020-01-02 12:58:00     41
2       10049083    2020-01-02 12:58:00     40
3       10049083    2020-01-02 12:58:00     40
4       10049083    2020-01-02 12:58:00     41
5       10049083    2020-01-02 12:58:00     40
6       10049083    2020-01-02 12:58:00     40
1       10061286    2020-01-02 05:44:00     41
1       10081536    2020-01-02 12:11:00     41
1       10092699    2020-01-02 19:12:00     41
1       10116976    2020-01-02 22:03:00     41

    Demo on dbfiddle