Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Generate the row count (serial number) of records after returning the result in MySQL query?
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)
Updated on: 30-Jul-2019
3K+ Views
Advertisements