MySQL - generate numbers for groups of a result

Question

I need a query to return this result:

+---------+-----+-------+
| ref_nid | nid | delta |
+---------+-----+-------+
|   AA    | 97  |  1    |
|   BB    | 97  |  2    |
|   CC    | 97  |  3    |
|   DD    | 98  |  1    |
|   EE    | 98  |  2    |
|   FF    | 98  |  3    |
+---------+-----+-------+

However, I do not have the delta column. I need to generate it for each nid group.

In other words, I need an auto incremented number for each group of the result.

Answer 1

Check out this guy's blog

select @rownum:=@rownum+1 ‘rank’, p.* from player p, (SELECT @rownum:=0) r order by score desc limit 10;

Basically,

set @i = 0; 
select id, @i:=@i+1 as myrow from mytable