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

sql sql-server rank
davomarti picture davomarti · Apr 6, 2012 · Viewed 9.2k times · Source

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

Answer

Abe Miessler picture Abe Miessler · Apr 6, 2012

Try using DENSE_RANK instead of RANK

select *, DENSE_RANK() over (order by apples) as theRank from #test

Related questions

SQL Server Query for Rank (RowNumber) and Groupings

I have a table that has some columns: User, Category, Value And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category. Example: user1 CategoryA 10 user2 …

Read More
How can I get row's index from a table in SQL Server?

I have a Toplist table and I want to get a user's rank. How can I get the row's index? Unfortunately, I am getting all rows and checking in a for loop the user's ID, which has a significant impact …

Read More
How to add rank column?

I would like to select records and determine rank number for each similar data. My data is as follows. MEMBER ID | LOAN AMOUNT 1 | 2,000.00 2 | 1,000.00 3 | 4,000.00 4 | 1,000.00 The result I wanted is shown below. RANK|MEMBER ID|LOAN AMOUNT 1 |3 |4,000.00 2 |1 |2,000.00 3 |2 |1,000.00 3 |4 |1,000.00 RANK is a new column. …

Read More