MySQL - Counter within group

mysql

Solution

User Defined Variables become handy when solving this issues. This should work:

select g, x, counter from (
    select g, x,
        @counter := if (g = @prev_g, @counter + 1, 1) counter,
        @prev_g := g
    from tb, (select @counter := 0, @prev_g := null) init
    order by g, x
) s

If you don't really mind the fourth column then you could safely remove the outer select. That would improve performance a lot.

Problem

I would like to add a counter for each row within a group according an ascending variable. I have a solution but it does not work if some variable within groups are equal: ``` CREATE TABLE tb ( g CHAR(1) , x INTEGER ); INSERT INTO tb (g, x) VALUES ('a',1) , ('a',2) , ('a',10) , ('b',1) , ('b',1) , ('b',10) ; SELECT g,x, (SELECT COUNT(*) FROM tb b WHERE a.g = b.g AND a.x > b.x ) + 1 AS counter FROM tb a ; ``` What I get is: ``` -------------------- | g | x | counter | -------------------- | a | 1 | 1 | | a | 2 | 2 | | a | 10 | 3 | | b | 1 | 1 | | b | 1 | 1 | | b | 10 | 3 | -------------------- ``` But I would like to get: ``` -------------------- | g | x | counter | -------------------- | a | 1 | 1 | | a | 2 | 2 | | a | 10 | 3 | | b | 1 | 1 | | b | 1 | 2 | | b | 10 | 3 | -------------------- ``` Any idea how I can solve this problem? Thanks a lot for hints. giordano

Original source