Generate the row count (serial number) of records after returning the result in MySQL query?

MySQLMySQLi Database

To generate serial number i.e. row count in MySQL query, use the following syntax.

SELECT @yourVariableName − = @yourVariableName+1 anyAliasName,
   yourColumnName1,yourColumnName2,yourColumnName3,....N from yourTableName ,
   (select @yourVariableName − = 0) as yourVariableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table tblStudentInformation
   -> (
   -> StudentName varchar(20),
   -> StudentAge int,
   -> StudentMathMarks int
   -> );
Query OK, 0 rows affected (0.68 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into tblStudentInformation values('Carol',23,89);
Query OK, 1 row affected (0.18 sec)

mysql> insert into tblStudentInformation values('Bob',25,92);
Query OK, 1 row affected (0.22 sec)

mysql> insert into tblStudentInformation values('John',21,82);
Query OK, 1 row affected (0.15 sec)

mysql> insert into tblStudentInformation values('David',26,98);
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from tblStudentInformation;

The following is the output.

+-------------+------------+------------------+
| StudentName | StudentAge | StudentMathMarks |
+-------------+------------+------------------+
| Carol       | 23         |               89 |
| Bob         | 25         |               92 |
| John        | 21         |               82 |
| David       | 26         |               98 |
+-------------+------------+------------------+
4 rows in set (0.00 sec)

The following is the query to generate the serial number in MySQL query −

mysql> SELECT @serialNumber − = @serialNumber+1 yourSerialNumber,
   -> StudentName,StudentAge,StudentMathMarks from tblStudentInformation,
   -> (select @serialNumber − = 0) as serialNumber;

Here is the output displaying the row number in the form of serial number.

+------------------+-------------+------------+------------------+
| yourSerialNumber | StudentName | StudentAge | StudentMathMarks |
+------------------+-------------+------------+------------------+
|                1 | Carol       |         23 |               89 |
|                2 | Bob         |         25 |               92 |
|                3 | John        |         21 |               82 |
|                4 | David       |         26 |               98 |
+------------------+-------------+------------+------------------+
4 rows in set (0.00 sec)
raja
Published on 31-Jan-2019 12:12:28
Advertisements