ROW_NUMBER() in MySQL with Example

Home > SQL > ROW_NUMBER() in MySQL with Example

ROW_NUMBER() in MySQL with Example

May 06 2020 by Admin

In this article, we have described how to use ROW_NUMBER() in MySQL with a sample example.

In this article, we have described how to use Row_Number in MySQL with an example. We have created a TEMPORARY table in this example and taken some records for this activity. We have used the FirstName for the partition of the row_number. You can see the table script below.

Table Script

DROP TEMPORARY TABLE IF EXISTS Temp_UserInfo;

CREATE TEMPORARY TABLE Temp_UserInfo

(Id int

,FirstName varchar(150)

,LastName varchar(150)

,EmailId varchar(100)

);

Table Records

We have shared some records for insert.

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (1,'Admin','Admin','Admin@MyInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (2,'James','Smith','James@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (3,'Michael','Smith','Michael@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (4,'Robert','Smith','Robert@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (5,'David','Smith','David@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (6,'Maria','Rodriguez','Maria@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (7,'Maria','Rodriguez','Maria@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (8,'Mary','Rodriguez','Mary@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (9,'David','Smith','David@myInfo.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (10,'Michael','Smith','Michael@Info.com');

INSERT INTO Temp_UserInfo(Id,FirstName,LastName,EmailId)

VALUES (11,'Michael','Smith','Michael@Info.com');

Query Script

We have used case statement for check firstname exists or not and after that set row number incremental.

#Row Number partition By First name

SET @rowv=0;

SET @PreFirstName='';

SELECT CASE WHEN t.FirstName=@PreFirstName

THEN @rowv:= @rowv + 1

ELSE @rowv:=1 END 'RowNumber'

,@PreFirstName :=FirstName

,t.LastName

,t.EmailId

FROM Temp_UserInfo t ORDER BY FirstName ASC;

Output

ROW_NUMBER() in MySQL with Example

Download

MySQL
ROW_NUMBER

Note: All contents are copyright of their authors.

Related Articles

How to populate Gridview with MySQL database

9/23/2018 10:09:00 PM By Admin

1389 Views

How to use gridview in asp.net with C# using Mysql

2/1/2019 11:51:00 PM By Admin

2233 Views

How to use gridview with datatable in asp.net with C# using Mysql

2/3/2019 9:50:00 PM By Admin

1878 Views

Example How to bind Gridview data from database in MySQL

2/3/2019 10:21:00 PM By Admin

1786 Views

How to create a table (Insert, Update and Delete) in Sql

7/6/2019 10:58:00 AM By Admin

1406 Views

How to create a Stored Procedure in SQL

7/7/2019 6:06:00 AM By Admin

1289 Views

How to use Row_Number and Dense_Rank in MySQL

7/18/2019 11:28:00 AM By Admin

1535 Views

How to Json_Extract in MySQL with example

7/31/2019 10:57:00 AM By Admin

8538 Views

How to upload file and save Image in Binary format in SQL server

8/24/2019 12:23:00 AM By Admin

6075 Views

How to remove all the duplicate rows or records from a table in SQL server.

1/22/2020 12:02:00 PM By Admin

988 Views

Comments

Leave a Reply

Comment:^*

Please your Comment !

Email ID:^*

Enter Your Email Id ! Enter Your Validate Email Id !

Name:^*

Enter your Name !