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

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.

If you mean in a SELECT statement:

Say your select was

select * from tbl

It becomes

select @n := @n + 1 RowNumber, t.*
from (select @n:=0) initvars, tbl t

Notes:

1. select @n:=0 is used to reset the global variable to 0
2. @n := @n + 1 increases it by 1 for each row, starting from 1. This column is named "RowNumber"

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;

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.

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` 

You may want to look into this blog post: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/

Seems to have a solution for adding a row number to a query result, which might solve your problem.