MySQL get rank of duplicate values based on their create dates

Question

I'm looking for a way on how can I get the rank of the duplicate entries of my table based on their create dates. The older the date will be the one who will get the rank 1 and so on for the next duplicates. It should look like this:

id   number   create_date   rank
1      1         02/03        1
2      1         02/04        2
3      3         02/03        1
4      4         02/03        1
5      4         02/04        2
6      4         02/05        3

I tried searching for this but I can't understand well on how they implement it or more like it is not the way I wanted it to be. Hope someone can help me on this.

Answer 1

select
t.*,
@rank := if(@prevDate = create_date, @rank, @rank + 1) as rank,
@prevDate := create_date
from
your_table t 
, (select @rank := 0, @prevDate := null) var_init
order by create_date, id

Explanation:

Here

, (select @rank := 0, @prevDate := null) var_init

the variables are initalized. It's the same as writing

set @rank = 0;
set @prevDate = null;
select ... /*without the crossjoin*/;

Then the order of the columns in the select clause is important. First we check with this line

@rank := if(@prevDate = create_date, @rank, @rank + 1) as rank,

if the current row has the same date as the previous row. The @prevDate holds the value of the previous row. If yes, the @rank variable stays the same, if not it's incremented.

In the next line

@prevDate := create_date

we set the @prevDate variable to the value of the current row. That's why the order of the columns in the select clause is important.

Finally, since we're checking with the previous row, if the dates differ, the order by clause is important.