MySQL SET rank over First Column and Second Column

Question

I'm trying to make a query setting rank column by First and Second column. Like Rank over Partition which doesn't exist MySQL

For example,

From

+----+-------+--------+------+
| id | First | Second | Rank |
+----+-------+--------+------+
|  1 | a     |     10 |      |
|  2 | a     |      9 |      |
|  3 | b     |     10 |      |
|  4 | b     |      7 |      |
|  5 | a     |      1 |      |
|  6 | b     |      1 |      |
+----+-------+--------+------+

To

+----+-------+--------+------+
| id | First | Second | Rank |
+----+-------+--------+------+
|  1 | a     |     10 |    3 |
|  2 | a     |      9 |    2 |
|  3 | b     |     10 |    3 |
|  4 | b     |      7 |    2 |
|  5 | a     |      1 |    1 |
|  6 | b     |      1 |    1 |
+----+-------+--------+------+

The Rank doesn't continue. It starts from 1 again when it reaches the last value of 'a' of 'First' column.

And it's gotta be SET not SELECT. I wouldn't mind using SELECT but my point is I'm not trying to retrieve data from Database but setting values.

Cheers in advance mates.

Answer 1

Came up with a solution which I was looking for. I'm not sure if these queries are completely safe but so far no harms.

SET @rank = 0, @First = ''
UPDATE 'Table' SET 
rank = IF(@First = First, @rank:= @rank +1, @rank := 1 AND @First := First)
ORDER BY First ASC, Second;