What to do when the Rank function produces the gaps in the ranking system. for example if there is atie for 2nd rank than the rank function skips the 3rd rank and continues with 4th rank. what to do when you need all ranks witout any gaps
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)
use this query
SELECT Dense_RANK() OVER (ORDER BY Salary) AS [Rank by Salary], FirstName,
Salary FROM Employee
output
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
|
No responses found. Be the first to respond and make money from revenue sharing program.
|