Ranking rows using SQL Server Rank function without skipping a rank number
10,863
Try using DENSE_RANK instead of RANK
select *, DENSE_RANK() over (order by apples) as theRank from #test
Author by
davomarti
Updated on June 07, 2022Comments
-
davomarti almost 2 years
I want to rank rows in a table without skipping numbers in the rank. Please see below example.
CREATE TABLE #test( apples int NOT NULL, ) ON [PRIMARY] GO insert into #test( apples ) values ( 10 ) insert into #test( apples ) values ( 10 ) insert into #test( apples ) values ( 20 ) insert into #test( apples ) values ( 30 ) select *, RANK() over (order by apples) as theRank from #test drop table #test go
The result is
apples theRank 10 1 10 1 20 3 30 4
How can I get the rank to not skip the number 2, so that the result looks like
apples theRank 10 1 10 1 20 2<-- 30 3<--
I don't have to use the Rank function, as long as I get the desired ordering.
Thanks!!
-
Dan P about 12 yearsExact same answer to the T even spelling and punctuation. I'll delete my answer because it looks like a copy and paste of yours and you beat me by 2 mins.