The only difference between the Rank() and Dense_Rank() functions is that the Rank() function produces the gaps in the ranks in case of ties whereas the dense_rank function doesn't. Here is the example
CREATE TABLE
Employee(FirstName VARCHAR(10),Salary INT,)
INSERT INTO Employee VALUES ('Ted',2300)
INSERT INTO Employee VALUES ('John',4000)
INSERT INTO Employee VALUES ('George',600)
INSERT INTO Employee VALUES ('Mary',1100)
INSERT INTO Employee VALUES ('Sam',1700)
INSERT INTO Employee VALUES ('Doris',600)
INSERT INTO Employee VALUES ('Frank',3800)
INSERT INTO Employee VALUES ('Larry',500)
INSERT INTO Employee VALUES ('Sue',2900)
INSERT INTO Employee VALUES ('Sherry',1100)
INSERT INTO Employee VALUES ('Marty',2300)
SELECT Dense_RANK() OVER (ORDER BY Salary) AS [Rank by Salary], FirstName, Salary FROM Employee
Dense Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 500
2 Doris 600
2 George 600
3 Mary 1100
3 Sherry 1100
4 Sam 1700
5 Ted 2300
5 Marty 2300
6 Sue 2900
7 Frank 3800
8 John 4000
SELECT RANK() OVER (ORDER BY Salary) AS [Rank by Salary],
FirstName,
Salary
FROM employee
Rank by Salary FirstName Salary
-------------------- ---------- -----------
1 Larry 500
2 Doris 600
2 George 600
4 Mary 1100
4 Sherry 1100
6 Sam 1700
7 Ted 2300
7 Marty 2300
9 Sue 2900
10 Frank 3800
11 John 4000
|
No responses found. Be the first to respond and make money from revenue sharing program.
|