March 18, 2011
how can I add a new column which counts the number of rows as serial number
Question by sadi
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.
Answer by RichardTheKiwi
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:
select @n:=0is used to reset the global variable to 0@n := @n + 1increases it by 1 for each row, starting from 1. This column is named “RowNumber”
Answer by Starx
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`
