Ranking rows using SQL Server Rank function without skipping a rank number

Go To StackoverFlow.com

5

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!!

2012-04-05 23:49
by davomarti


9

Try using DENSE_RANK instead of RANK

select *, DENSE_RANK() over (order by apples) as theRank from #test
2012-04-05 23:53
by Abe Miessler
Exact 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 - Dan P 2012-04-05 23:57
Lol great minds.. - Abe Miessler 2012-04-06 01:54
Excellent!!! Thank you very much - davomarti 2012-04-06 04:35
Ads