CODEWITHSUNDEEP
mysqlsql
user3882752
user3882752

Reputation: 273

increment numbers on each group of IDs

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.

Upvotes: 2

Views: 2524

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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;

Upvotes: 3

splash58
splash58

Reputation: 26153

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

Upvotes: 0

Related Questions