Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

Ranking functions

Modified: 2008/09/22 14:30 by vanthoog - Categorized as: SQL Server
In SQL Server 2005 4 ranking functions, ROW_NUMBER, RANK, DENSE_RANK and NTILE, have been added. These functions are similar and return a “sequence number” for every row in a result set. This article contains a brief description of the ranking functions ROW_NUMBER, RANK and DENSE_RANK. The ranking function NTILE is excluded from this article because it behaves slightly different.

The examples in this article use the following table and data:

create table person
(
	firstname nvarchar(16),
	lastname nvarchar(16),
	address nvarchar(16),
	city nvarchar(16)
	
)

insert into person values ( 'Bill' , 'Doe' , 'Amberroad 12' , 'Washington D.C.' )
insert into person values ( 'John' , 'Doe' , 'Main street 15' , 'Denver' )
insert into person values ( 'Mary' , 'Smith' , 'Broadway av 4' , 'New York' )
insert into person values ( 'Mary' , 'Jones' , 'Rodeo drive 10' , 'Beverly Hills' )
insert into person values ( 'Julia' , 'McAndrew' , 'Sunset blvrd 11' , 'Miami' )

Let’s start with the function ROW_NUMBER. The function ROW_NUMBER returns a straightforward and unique sequence number for every row in the result set. Here is for example a simple query returning all rows in the table:

select no = row_number() over (order by lastname) , * from person

no                   firstname        lastname         address          city
-------------------- ---------------- ---------------- ---------------- ----------------
1                    Bill             Doe              Amberroad 12     Washington D.C.
2                    John             Doe              Main street 15   Denver
3                    Mary             Jones            Rodeo drive 10   Beverly Hills
4                    Julia            McAndrew         Sunset blvrd 11  Miami
5                    Mary             Smith            Broadway av 4    New York

(5 row(s) affected)

The result set shows that a unique sequence number has been assigned to every row. The ROW_NUMBER requires the use of an “order by” clause, which determines the order in which the sequence number is assigned. By default the result set is ordered in the same way, but you can override this by using a separate “order by“ clause. And in both “order by” clauses you can use asc(ending) or desc (ending).

select no = row_number() over (order by lastname desc) , * from person order by city asc

no                   firstname        lastname         address          city
-------------------- ---------------- ---------------- ---------------- ----------------
3                    Mary             Jones            Rodeo drive 10   Beverly Hills
5                    John             Doe              Main street 15   Denver
2                    Julia            McAndrew         Sunset blvrd 11  Miami
1                    Mary             Smith            Broadway av 4    New York
4                    Bill             Doe              Amberroad 12     Washington D.C.

(5 row(s) affected)

In this example the result is ordered by city ascending, but the sequence number is assigned according to the descending ordering of the column lastname.

If you want to assign the same sequence number to columns with the same value you should use the function RANK. The function RANK works in exactly the same way as ROW_NUMBER except that it does not assign unique sequence numbers but uses the same sequence number for columns with the same value.

select no = rank() over (order by lastname) , * from person

no                   firstname        lastname         address          city
-------------------- ---------------- ---------------- ---------------- ----------------
1                    Bill             Doe              Amberroad 12     Washington D.C.
1                    John             Doe              Main street 15   Denver
3                    Mary             Jones            Rodeo drive 10   Beverly Hills
4                    Julia            McAndrew         Sunset blvrd 11  Miami
5                    Mary             Smith            Broadway av 4    New York

(5 row(s) affected)

In this example the sequence number 1 is assigned to 2 rows because in both rows the column lastname has the same value (being Doe).

The disadvantage of the function RANK is that a gap may appear in the sequence numbers. For example, in the last example the sequence number 2 is missing. If you need a continuous sequence of numbers, use the function DENSE_RANK. The function DENSE_RANK works in exactly the same way as RANK except that it returns a continuous sequence of numbers without any gaps.

select no = dense_rank() over (order by lastname) , * from person

no                   firstname        lastname         address          city
-------------------- ---------------- ---------------- ---------------- ----------------
1                    Bill             Doe              Amberroad 12     Washington D.C.
1                    John             Doe              Main street 15   Denver
2                    Mary             Jones            Rodeo drive 10   Beverly Hills
3                    Julia            McAndrew         Sunset blvrd 11  Miami
4                    Mary             Smith            Broadway av 4    New York

(5 row(s) affected)

In this example a continuous sequence of numbers (from 1 to 4) without any gaps is returned, while the sequence number 1 is still assigned to 2 rows.

In conclusion, the functions ROW_NUMBER, RANK and DENSE_RANK are very similar in use and result. They only differ in uniqueness and continuity of the returned sequence numbers.

                                ROW_NUMBER RANK DENSE_RANK
Unique sequence number          Yes        No   No
Continuous sequence numbers     Yes        No   Yes



 © Evident Interactive BV