Enumerate rows in mysql based on groups

Question

I have a set of records, for example

A   B
1   5
1   6
1   9
2   1
2   8

where we have two groups (A=1 and A=2) and inside groups record are ordered by values in B.

And I need to add a column with enumeration inside each group

A   B   C
1   5   1
1   6   2
1   9   3
2   1   1
2   8   2

I tried to use nested query to fetch a number of records that have a value in B less than for current record given the same group id (A) but it is too slow. I use some enumeration of rows in other part of code (using select @rownum:=@rownum+1 num) but I enumerate all records there. So I am interested, is it possible to implement it iside query (if possible I'd like to avoid cursor loops etc). Thanks!

Answer 1

Prior to the introduction of common table expressions in MySQL 8, GL's first method would be fastest, but here's a couple of other ideas (also using older technology), just for fun...

1.

SELECT x.*
     , COUNT(*) rank 
  FROM my_table x 
  JOIN my_table y 
    ON y.a = x.a 
   AND y.b <= x.b 
 GROUP  
    BY x.a
     , x.b;

2.

SELECT x.* 
     , FIND_IN_SET(b,n) rank 
  FROM my_table x 
  JOIN 
     ( SELECT a 
            , GROUP_CONCAT(b ORDER BY b) n 
         FROM my_table 
        GROUP 
           BY a
     ) y 
    ON y.a = x.a;

Answer 2

The most efficient way to do this is using variables:

select t.a, t.b,
       (@rn := if(@a = t.a, @rn + 1,
                  if(@a = t.a, 1, 1)
                 )
       ) as c
from table t cross join
     (select @a := '', @rn := 0) params
order by t.a, t.b;

An alternative approach uses a correlated subquery or join but assumes that the b values are unique for each a (or there is a unique column in the table):

select t.a, t.b,
       (select count(*) from table t2 where t2.a >= t.a) as c
from table t;