Menu
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!!
984
The rank of a row is one plus the number of ranks that come before the row in question. ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).
rank and dense_rank are similar to row_number , but when there are ties, they will give the same value to the tied values. rank will keep the ranking, so the numbering may go 1, 2, 2, 4 etc, whereas dense_rank will never give any gaps.
The row_number gives continuous numbers, while rank and dense_rank give the same rank for duplicates, but the next number in rank is as per continuous order so you will see a jump but in dense_rank doesn't have any gap in rankings.
Try using DENSE_RANK instead of RANK
select *, DENSE_RANK() over (order by apples) as theRank from #test
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
