DROP TEMPORARY TABLE IF EXISTS Temp_UserInfo;
CREATE TEMPORARY TABLE Temp_UserInfo
( Id int
,FirstName varchar(150)
,LastName varchar(150)
,EmailId varchar(100)
);
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 (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');
#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;