Ranking Function - Dense_Rank()

Dense_Rank() :- The DENSE_RANK function is similar to the RANK function, although this function doesn’t produce gaps in the ranking numbers. Instead this function sequentially ranks each unique ORDER BY value. With the DENSE_RANK function each row either has the same ranking as the preceeding row, or has a ranking 1 greater then the prior row.

CREATE TABLE Emp(
EmpName VARCHAR(9),
Age INT,
MaritalStatus char(1))

INSERT INTO Emp VALUES ('Abhinav',40,'S')
INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

SELECT Dense_RANK() OVER (ORDER BY Age) AS [Rank by Age],
EmpName,
Age
FROM Emp

Rank by Age EmpName Age
-------------------- --------- -----------
1 Bill 11
2 Dhvani 20
2 Nehal 20
3 R 30
4 Abhinav 40
4 Suvrendu 40
5 Nirmal 50
6 Sunil 95
7 Ram 100

(9 row(s) affected)

SELECT Dense_RANK() OVER (PARTITION BY MaritalStatus ORDER BY Age) AS [Partition by MaritalStatus],
EmpName,
Age,
MaritalStatus
FROM emp

Partition by MaritalStatus EmpName Age MaritalStatus
-------------------------- --------- ----------- -------------
1 Dhvani 20 M
1 Nehal 20 M
2 Suvrendu 40 M
3 Sunil 95 M
1 Bill 11 S
2 R 30 S
3 Abhinav 40 S
4 Nirmal 50 S
5 Ram 100 S

(9 row(s) affected)