Choose incremented integer

Question

I want to know if incremental integer can be selected from mysql table? and if possible, how can I achieve this?

My thing is, I have a bunch of data and I need to do INSERT INTO newtable ... SELECT somefield FROM sometable . However, there is one field in newtable called counter , which I need as an incremental integer, for example:

 row #1: counter=1 row #2: counter=2 row #3: counter=3 row #4: counter=4 row #5: counter=5 row #6: counter=6 ... and so on... 

I can do this using a simple php script, but I want to try to do all this from a mysql query. So can you guys tell me if this is possible?

Answer 1

Try the following:

 INSERT INTO newTable(someField, counter) SELECT a.someField, (@rank: =@rank +1) AS counter FROM sometable a INNER JOIN (SELECT @rank :=0) b