rank function always brings back contiguous ranking
I have a simple table that has ids in them.
1
5
22
When I do the following
select *, rank() over (order by id) as rank from my table
the rank comes back as
1
2
3
I don’t understand why the ranking is contiguous? I was expecting something like
1
4
17
I would have expected the contiguous behavior from the dense_rank function.
3 Solutions collect form web for “rank function always brings back contiguous ranking”
All ID’s are different, that is the reason for that behaviour. Ranking functions come into play when the values you order by are equal.
create table TableName(id int);
insert into TableName values(1);
insert into TableName values(5);
insert into TableName values(5);
insert into TableName values(22);
insert into TableName values(22);
insert into TableName values(22);
select *,
rank() over (order by id) as rank,
dense_rank() over (order by id) as dense_rank,
row_number() over (order by id) as row_num
from TableName
ID RANK DENSE_RANK ROW_NUM
1 1 1 1
5 2 2 2
5 2 2 3
22 4 3 4
22 4 3 5
22 4 3 6
Demo
Ranking Functions (Transact-SQL)
Are the id’s contiguous? If there are no ties, then RANK and DENSE_RANK will return the same values.
Try this instead:
;WITH CTE
AS
(
SELECT *,
RANK() OVER(ORDER BY id) as rank
FROM tablename
)
SELECT
c1.Id,
(c1.id - ISNULL(c2.ID, 0)) rank
FROM CTE c1
LEFT JOIN cte c2 ON c1.rank - c2.rank = 1;