MySQL: how to sort rows by a field and assign another field a counter?

Question

Imagine there's a table with the following contents:

 x      y     z
 aa     5     null
 bb     2     null
 cc     5     null
 dd     1     null

I want to sort the rows by y and assign an auto-incremented field to z, so in this case the end result (the changed table) would be

 x      y     z
 dd     1     1
 bb     2     2
 aa     5     3
 cc     5     4

or

 x      y     z
 aa     5     3
 bb     2     2
 cc     5     4
 dd     1     1

How do I do that?

So to make it clear, I want to change the table, not get that stuff to code.

As requested, http://sqlfiddle.com/#!2/cd610/1

Answer 1

update your_table t1
inner join
(
  select id, @rank := @rank + 1 as r
  from your_table, (select @rank := 0) r
  order by y
) t2 on t2.y = t.y
set z = r

SQLFiddle demo