CODEWITHSUNDEEP
mysqlphpmyadmin
sadi
sadi

Reputation: 611

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.

Upvotes: 17

Views: 23649

Answers (6)

Reenu Singh
Reenu Singh

Reputation: 1

  1. Assigning sequential numbers to rows

The following statement uses the ROW_NUMBER() function to assign a sequential number to each row from the products table:

linkhere try this: row_number

row_number() over(order by column_name)

example: SELECT ROW_NUMBER() OVER ( ORDER BY productName ) row_num, productName, msrp FROM products ORDER BY productName;

Upvotes: 0

Schaltwert
Schaltwert

Reputation: 119

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;

Upvotes: 11

Norman H
Norman H

Reputation: 2262

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.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

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"

Upvotes: 27

Starx
Starx

Reputation: 79021

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`

Upvotes: 0

Alex
Alex

Reputation: 6376

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.

Upvotes: 0

Related Questions