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

23-09-2018 22:09:00 By Admin

1527 Views

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

01-02-2019 23:51:00 By Admin

2450 Views

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

03-02-2019 21:50:00 By Admin

2091 Views

Example How to bind Gridview data from database in MySQL

03-02-2019 22:21:00 By Admin

1957 Views

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

06-07-2019 10:58:00 By Admin

1608 Views

How to create a Stored Procedure in SQL

07-07-2019 06:06:00 By Admin

1480 Views

How to use Row_Number and Dense_Rank in MySQL

18-07-2019 11:28:00 By Admin

1807 Views

How to Json_Extract in MySQL with example

31-07-2019 10:57:00 By Admin

8733 Views

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

24-08-2019 00:23:00 By Admin

6349 Views

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

22-01-2020 12:02:00 By Admin

1110 Views

Comments

Leave a Reply

Comment:^*

Please your Comment !

Email ID:^*

Enter Your Email Id ! Enter Your Validate Email Id !

Name:^*

Enter your Name !