MySQL query to select results with auto increment as a new column added in the result

Question

I have a student's table with the following fields:

student(student_id, student_name, student_avg)

I need to write a query in MySQL which will display the result as :

Serial no. => the result should also have a new column with serial number as 1,2,3,...,n like an auto increment for each row in the result.

student_id
student_name
student_avg > 4

I don't want to alter my table in any way. All I have to do is write a query which will give me the above result. I hope I am clear.

Example data:

student_id         student_name      student_avg 
 1                    abc               2.5
 2                    xyz               4.1
 3                    def               4.2

Sample output after querying:

serial_no    student_id    student_name     student_avg
  1             2            xyz               4.1
  2             3            def               4.2

student_id student_name student_avg 1 abc 2.5 2 xyz 4.1 3 def 4.2 sample output serial_no student_id student_name student_avg 1 2 xyz 4.1 2 3 def 4.2 — Aayush, Aug 08 '12 at 09:56
AFAIK there're window functions `rank() over(...)` and `row_number()` etc. for mysql 8.0 — Sergey Benner, Mar 19 '19 at 12:09

score 44 · Accepted Answer · edited May 23 '17 at 10:30

44

Try this on

SELECT  @s:=@s+1 serial_number,student_id,student_name,student_avg
FROM    students,
        (SELECT @s:= 0) AS s
WHERE
student_avg > 4;

https://stackoverflow.com/a/11096550/1423506

edited May 23 '17 at 10:30

Community

1
1

answered Aug 08 '12 at 10:08

1

Found something similar before. There is one problem for me though: when I combine this with another `order by`, the incremental numbering is not sorted anymore, it's applied before the ordering apparently. Any ideas? – fluxon Nov 04 '17 at 11:22

score 11 · Answer 2 · answered Aug 08 '12 at 10:00

SET @serial=0;
SELECT @serial := @serial+1 AS `serial_number`, `column_name` FROM `table_name`;

In your particular case:

SET @serial=0;

SELECT 
   @serial := @serial+1 AS `serial_number`, 
   `student_id`, 
   `student_name`, 
   `student_avg`
FROM 
   `students`
WHERE
   `student_avg` > 4;

MySQL query to select results with auto increment as a new column added in the result

2 Answers2

Linked