Generate serial number in mysql query

Question

I have a table: student_marks

marks
-----
  44
  55
  64
  98
  76

Expected output:

serial_number|marks
--------------------
  1          | 44
  2          | 55
  3          | 64
  4          | 98
  5          | 76

Using mysql user defined variables, it could be done using query:

 set  @a:=0;select @a:=@a+1 serial_number, marks from student_marks;

Is there any way to achieve this in msyql without using user defined variables?

Answer 1

Based on your reasons for not wanting to use user defined variables as wanting to avoid having 2 queries, one for inializing and one to use it you could use the following:

SELECT  @a:=@a+1 serial_number, 
        marks 
FROM    student_marks,
        (SELECT @a:= 0) AS a;

Answer 2

Best Answer to this question should be the following, the best practice

SET @count:=0;
SELECT  (@count:=@count+1) AS serial_number, 
        marks 
FROM    student_marks

Answer 3

I had a table with a column(c5) has contain a number x, I need a sql expression that repeated the same row x numbers of times:

my table A contains:

c1  c2  c3  c4  c5
16  1   2   16  3
16  1   2   17  2 
16  1   2   18  1

and I need:

c1  c2  c3  c4  c5  n
16  1   2   16  3   1
16  1   2   16  3   2
16  1   2   16  3   3
16  1   2   17  2   1
16  1   2   17  2   2
16  1   2   18  1   1

I solved That with the expression:

SELECT
    c1, c2, c3, c4, c5, row_number AS n
FROM
    (
        SELECT
            @curRow := @curRow + 1 AS row_number
        FROM
            tablea
        JOIN (SELECT @curRow := 0) r
        WHERE
            @curRow < (
                SELECT
                    max(field1)
                FROM
                    tablea
            )
    ) AS vwtable2
LEFT JOIN tablea d ON vwtable2.row_number <= tablea.field1;

Answer 4

tested in mysql 5.6:

SELECT
    @a:=@a+1 as serial_number,
    student.* 
FROM
    student
join (SELECT @a:= 0) a

Answer 5

you can add serial number with this query :

set @counter=0;
SELECT  @counter:=@counter+1 as serial_number from <table_name>

output :

   |...............................|
   | serial_number  |   first_name |
   |...............................|
   |      1         |    mojtaba   |
   |      2         |    athena    |
   |      3         |     azin     |
   |      4         |     ava      |
   |      5         |    arash     |
   |...............................|

in addition if you want to use in where

set @counter=0;
select serial_number from (SELECT  @counter:=@counter+1 as serial_number 
from <table_name>) as tbl_counter where serial_number > 3

output :

   |...............................|
   | serial_number  |   first_name |
   |...............................|
   |      4         |     ava      |
   |      5         |    arash     |
   |...............................|

Answer 6

I found simple way to generate serial number

select ROW_NUMBER() over (PARTITION by '1') AS serial_number,customer_id from users;

Also it support pagination

Answer 7

No, there isn't. But you could produce the serial_number in program side, not the database side.

Answer 8

None of the above answers seemed satisfactory as you would need to initialize the variable as null in a separate query.

So posting the below all encompassing solution:

SELECT ROUND(@a:=@a+1,0) sr_num, t.* from (select @a:=0) initvars, your_table t;

Answer 9

@a:=@a+1 serial_no,

CROSS JOIN (SELECT @a:= 0) AS a

SELECT @a:=@a+1 serial_no, sc.id, sc.name, sc.address, c.country,s.state 
FROM oc_table1 sc CROSS JOIN (SELECT @a:= 0) AS a LEFT JOIN oc_table2 .......

Answer 10

ALTER TABLE table_name ADD COLUMN sr_no INT UNIQUE NOT NULL AUTO_INCREMENT FIRST;

This will even edit your main table in case you forgot to create a column for serial numbers. You can also use this to add sr_no column to two different columns and easily join them (or create a new table using both of them).

Answer 11

Why don't you make it an auto-increment field?

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Answer 12

No, unless you have a column which containes these numbers.

Answer 13

Though very late for the answer, generating the sequential ids without having two queries and without using joins and without using sub query.

SELECT *, (@cnt := if(@cnt IS NULL, 0,  @cnt) + 1) AS id FROM table_name;

Cheers