MySQL query enumerate rows depending on another field

Question

I have this structure:

table1    table2
id_t1     id_t2
id_t2

Now, I need to enumerate the rows within table2 but by the id_t2 in a single query.

Example of the output:

ennum  id_t1  id_t2
1      1      1
2      2      1
3      3      1
1      4      2
2      5      2
4      6      1

Leading to rows enumerated with exclusive indexes for each id_t2.

I know I have to use variables (like @ennum) and I have been able to enumerate them globally, but I haven't discovered the right way to do this yet.

Thanks in advance.

Answer 1

Ok, thanks to @JW. I think I got it:

For this to work in phpmyadmin:

1. it is necessary to set variables running1 and previous1 to 0 before.
2. the nested query has to be ordered by idt2 as if not it will not update correctly the running index.

Result is:

SET @running1:=0;
SET @previous1:=0;
SELECT  g.ennum, 
    g.id_t1,
    g.id_t2
FROM 
    (
        SELECT  t.id_t1,
                t.id_t2,
                @running1:=if(@previous1=concat(t.id_t2),@running1,0) + 1 as ennum  ,
                @previous1:=concat(t.id_t2) 
        FROM TableName t
        ORDER BY id_t2
    ) AS g
ORDER BY id_t2, ennum