Alex Rivera | Logout

how can I add a new column which counts the number of rows as serial number

Asked 2011-03-18T11:56:00.363
17
record of
id  fare    commission  routecode   vehicle number  productcode date    time    driver  owner name
15  12345   123 4533    1   3344    2011-03-18  00:00:00    yasir   saleem
20  a   a   3433    1   2333    2011-03-25  00:00:00    yasir   saleem
36  11111   11111   3433    1   2333    2011-03-25  16:13:12    yasir   saleem
9   1233    123 3433    nk-234  2333    2011-03-24  00:00:00    siddiq  aslam
21  1200    120 4533    nk-234  7655    2011-03-24  00:00:00    siddiq  aslam
22  1200    133333  0987    nk-234  2333    2011-03-11  00:00:00    siddiq  aslam
23  10000   11  4533    nk-234  7655    2011-03-19  00:00:00    siddiq  aslam
25  122 12  0987    nk-234  2333    2011-03-11  00:00:00    siddiq  aslam
26  1000    100 3344    nk-234  7655    2011-03-11  00:00:00    siddiq  aslam
27  1000    100 3344    nk-234  2333    2011-03-10  00:00:00    siddiq  aslam
34  100 10  3344    nk-234  2333    2011-03-18  00:00:00    siddiq  aslam
35  100 10  3344    nk-234  2333    2011-03-02  00:00:00    siddiq  aslam
5   1000    100 1234    wq1233  3344    2011-03-10  22:30:00    waqas   sami
6   2222    22  1234    wq1233  3344    2011-03-17  22:30:00    waqas   sami
24  a   a   4533    PSS-1234    7655    2011-03-02  00:00:00    salman  salam
42633   145175

I want to add another column before id which counts the number of

rows. It should start from 1 and increment by 1 for each row.

mysql phpmyadmin
Edit
Report

3 Answers

11

Add a new column mySerial to the table myTable and increment each row by 1 (starting at '1'):

ALTER TABLE myTable ADD mySerial int(11) DEFAULT '0' NOT NULL;
SELECT @n:=0;
UPDATE myTable SET mySerial = @n := @n + 1;
answered 2012-07-11T11:10:49.470
0

So, you want to add a column to every row with the rowcount in it? It's not possible to do that automatically, but you can add a column and update it on every insert (UPDATE table SET (rowcount = SELECT COUNT(*) FROM TABLE)) but I wonder why you want to do that? It seems to me you want to workaround something and I think there must be a better solution than adding a rowcount column.

answered 2011-03-18T12:00:07.707
0

I am not sure if i understand your question completely, but to add a column infront of id run this query

ALTER TABLE `yourtablename` ADD `yournewfield` VARCHAR( 50 ) NOT NULL BEFORE `id`
answered 2011-03-18T12:01:52.450

Your Answer