How to add row number in a query result set.

Sometimes we may need to add row number in query resultset. We can use the below query for automatically addition of row number in resultset.

Suppose we have a table student.

SELECT * FROM STUDENT;

Output

We need to add row number in this table. We can use the below query for that purpose in MySQL.

SELECT @Rownum:= @Rownum + 1 Row_Number, A.* FROM student A, (SELECT@Rownum:=0) B

Output:

In SQL Server, we can use the below query.

SELECTROW_NUMBER()OVER (ORDERBY StudentNo), a.*FROM dbo.Student a;

Subhashish Chaki , Sr Developer, Mindfire Solutions.

Author : Subhashish Chaki