Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select incremented integer

Tags:

integer

mysql

I want to know if it's possible to select incremented integer from mysql table? and if it does possible, how can I achieve that?

My case is, I have a bunch of data and I need to do INSERT INTO newtable ... SELECT somefield FROM sometable. However, there is one field on newtable called counter that I need it in incremented integer, eg:

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 simple php script, but I want to try to do it all from mysql query. So, can you guys tell me if it's possible?

like image 848
ariefbayu Avatar asked Feb 22 '11 04:02

ariefbayu


People also ask

How do I increment a SQL SELECT statement?

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .

What is Autoincrement in SQL?

The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add.

Can we auto increment varchar in SQL?

No. If you really need this, you will have to generate ID manually.


1 Answers

Try this:

INSERT INTO newTable(someField, counter)
SELECT a.someField, (@rank:=@rank+1) AS counter
  FROM sometable a INNER JOIN 
       (SELECT @rank :=0) b
like image 193
Chandu Avatar answered Sep 17 '22 17:09

Chandu