MySQL - Counter within group

Question

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

score 4 · Accepted Answer · answered Nov 18 '13 at 19:11

4

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.

answered Nov 18 '13 at 19:11

Mosty Mostacho

42,742
16
96
123

1

Fantastic!!! Thanks a lot. This code contains a lot of new syntax, "commands" (init) and tricks I didn't know and I never saw in SQL-books. Do yo a good reference? Regarding performance: this could be a problem. I will run only the inner select and drop the column afterwards. Thanks again! – giordano Nov 19 '13 at 06:49
OK. I realized now that "init" is not a command but an alias. – giordano Nov 19 '13 at 13:40
The part `(select @counter := 0, @prev_g := null) init` can be omitted when defining the parameter before select: `SET @prev_g := NULL; SET @counter :=0;` . For someone who comes from procedural language this may be easier to understand. – giordano May 17 '21 at 15:46

score 0 · Answer 2 · answered May 17 '23 at 07:40

Since newer versions of MariaDB/MySQL using user defined variables can lead to wrong results. Use window functions instead of user defined variables:

SELECT tb.*
     , ROW_NUMBER() OVER(PARTITION BY g ORDER BY g,x) counter
FROM test0.tb

See here for more information why user defined variables are dangerouse.

MySQL - Counter within group

2 Answers2

Linked