I write a question and provide a table in output but I wish to every row of my table includes a number instantly that begins from 1. for instance , I would like that my table has "number" column that first row of my table have 1 for the reason that column , second row of my table have 2 for the reason that column , third row of my table have 3 for the reason that column , ... How do i do that ? thanks
** My DBMS is MySQL **
Make use of a variable and increment it like following.
set @num:=0; select *, @num:=@num+1 `Row` from names;
mysql> create table names( name varchar(10) primary key )engine=Innodb charset=utf8 collate utf8_general_ci; Query OK, 0 rows affected (0.09 sec) mysql> insert into names values('a'), ('b'), ('cat'), ('dog'), ('parrot'), ('bird'); Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> set @num:=0; select *, @num:=@num+1 `Row` from names; Query OK, 0 rows affected (0.00 sec) +--------+------+ | name | Row | +--------+------+ | a | 1 | | b | 2 | | bird | 3 | | cat | 4 | | dog | 5 | | parrot | 6 | +--------+------+ 6 rows in set (0.00 sec)
Note: If you are using
* make certain it precedes.
Build your primary key a car increment area. More information here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
AUTO_INCREMENT is what you're searching for
Such as this :
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM;
Obtained from here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
EDIT: In line with the comment someone may want to include that by hand. You choose the utmost value out of your filed, increment by 1 after which place it to your table.
SELECT MAX(id) FROM animals