How to use Row_Number and Dense_Rank in MySQL

Home > SQL > How to use Row_Number and Dense_Rank in MySQL

How to use Row_Number and Dense_Rank in MySQL

July 18 2019 by Admin

In this article, we have described how to use Row_Number and Dense_Rank in MySQL with an example.

In this article, we have described how to use Row_Number and Dense_Rank 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 @DenseRank=0;

SET @PreFirstName='';

SELECT CASE WHEN t.FirstName=@PreFirstName

THEN @rowv:= @rowv + 1

ELSE @rowv:=1 END 'RowNumber'

,CASE WHEN t.FirstName=@PreFirstName

THEN @DenseRank:=@DenseRank

ELSE @DenseRank:=@DenseRank + 1 END 'DenseRank'

,@PreFirstName :=FirstName

,t.LastName

,t.EmailId

FROM Temp_UserInfo t ORDER BY FirstName ASC;

SQL Row_Number
SQL Temptable

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

1392 Views

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

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

2237 Views

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

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

1886 Views

Example How to bind Gridview data from database in MySQL

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

1790 Views

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

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

1412 Views

How to create a Stored Procedure in SQL

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

1294 Views

How to use Row_Number and Dense_Rank in MySQL

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

1539 Views

How to Json_Extract in MySQL with example

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

8544 Views

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

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

6093 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

992 Views

Comments

Leave a Reply

Comment:^*

Please your Comment !

Email ID:^*

Enter Your Email Id ! Enter Your Validate Email Id !

Name:^*

Enter your Name !