increment numbers on each group of IDs

Question

The table below represents the data I have in a table.

+----+------+
| ID | Year |
+----+------+
|  1 | 2005 |
|  1 | 2006 |
|  2 | 2005 |
|  2 | 2007 |
|  2 | 2008 |
|  3 | 2005 |
|  4 | 2009 |
+----+------+

I want to write a query which will show the results below.

+----+----+------+
| ID |auto| Year |
+----+----+------+
|  1 |  1 | 2005 |
|  1 |  2 | 2006 |
|  2 |  1 | 2005 |
|  2 |  2 | 2007 |
|  2 |  3 | 2008 |
|  3 |  1 | 2005 |
|  4 |  1 | 2009 |
+----+----+------+

As you can see the auto field will display an increment auto numbering to each ID.

someone want to bang out a variables with grouping for this ? It is definitely a duplicate question x 1000 — Drew, Aug 31 '15 at 14:40
...or slower...`SELECT x.* , COUNT(*) rank FROM my_table x JOIN my_table y ON y.id = x.id AND y.year <= x.year GROUP BY x.id,x.year;` — Strawberry, Aug 31 '15 at 14:43
possible duplicate of [Row number per group in mysql](http://stackoverflow.com/questions/17939198/row-number-per-group-in-mysql) — jpw, Aug 31 '15 at 14:43
true that ↑ as it is my current hammer and everything is a nail — Drew, Aug 31 '15 at 14:44

score 3 · Answer 1 · edited Aug 31 '15 at 15:21

3

The best way to do this in MySQL is to use variables. But, if you use variables, all the assignments need to be in the same expression because MySQL does not guarantee the order of evaluation of expressions in a SELECT.

So I recommend:

select id, 
       (@rn := if(@i = id, @rn + 1,
                  if(@i := id, 1, 1)
                 )
       ) as auto,
       year
from tableX cross join
     (select @i := -1, @rn := 0) params
order by id, year;

edited Aug 31 '15 at 15:21

Drew

24,851
10
43
78

answered Aug 31 '15 at 14:54

Gordon Linoff

1,242,037
58
646
786

removed possible 1064 error (Gordon types fast), reshuffled columns, this matches desired results, and is the safe way to do it without assuming variables are as you think they are, as Gordon said above in comments. – Drew Aug 31 '15 at 15:23

score 0 · Accepted Answer · answered Aug 31 '15 at 14:47

0

select  if(@i = id, @n:=@n+1, @n:=1) auto, @i:=id id, year 
    from thetable cross join (select @i:="") i cross join (select @n:=1) n 
  order by id, year

answered Aug 31 '15 at 14:47

splash58

26,043
3
22
34

1

Variables are the right approach, but if you use them, then they should all go into a single expression. MySQL does not guarantee the order of evaluation of expressions in a `SELECT`. – Gordon Linoff Aug 31 '15 at 14:55
@Drew i see there the result from the question – splash58 Aug 31 '15 at 14:57
Works for me. Thanks! – user3882752 Aug 31 '15 at 15:02
4

but it is the wrong and dangerous approach as @GordonLinoff said. Read [this](http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/) – Drew Aug 31 '15 at 15:04

increment numbers on each group of IDs

2 Answers2