Home > SQL Server 2005 > RANKING FUNCTIONS | ROW_NUMBER, RANK, DENSE_RANK, NTILE

RANKING FUNCTIONS | ROW_NUMBER, RANK, DENSE_RANK, NTILE


SQL Server 2005 provides functionality for using ranking functions with your result set. One can select a number of ranking algorithms which are applied to a column of your table that you want to classify in a scope of your executing query. This feature is dynamic and upon change of data (addition or removal of rows) it gives desired results the next time query is run.

Its 4 gems are:
1. ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax:

ROW_NUMBER() OVER ( [ < partition_by_clause > ] < order_by_clause > )

2. RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Syntax:

RANK() OVER ( [ < partition_by_clause > ] < order_by_clause > )

3. DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Syntax:

DENSE_RANK() OVER( [ ] < order_by_clause > )

4. NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax:

NTILE(integer_expression) OVER( [ ] < order_by_clause > )

Now lets take an example, simple one of a class of students, their marks & class:

select 'A' [class], 80 [marks], 'manoj' stuName
into #tempTable
UNION
select 'A', 70 [marks],'harish' stuName
UNION
select 'A', 80 [marks],'kanchan' stuName
UNION
select 'A', 90 [marks],'pooja' stuName
UNION
select 'A', 90 [marks],'saurabh' stuName
UNION
select 'A', 50 [marks],'anita' stuName
UNION
select 'B', 60 [marks],'nitin' stuName
UNION
select 'B', 50 [marks],'kamar' stuName
UNION
select 'B', 80 [marks],'dinesh' stuName
UNION
select 'B', 90 [marks],'paras' stuName
UNION
select 'B', 50 [marks],'lalit' stuName
UNION
select 'B', 70 [marks],'hema' stuName

select * from #tempTable
Now on selection this gives you:
class 	marks   name
A 	50 	anita
A 	70 	harish
A 	80 	kanchan
A 	80 	manoj
A 	90 	pooja
A 	90 	saurabh
B 	50 	kamar
B 	50 	lalit
B 	60 	nitin
B 	70 	hema
B 	80 	dinesh
B 	90 	paras

The following query shows you how each function works:

select marks, stuName,
    row_number() over(order by marks desc) as [RowNumber],
    rank() over(order by marks desc) as [Rank],
    dense_rank() over(order by marks desc) as [DenseRank],
    ntile(3) over(order by marks desc) as [NTile]
from #tempTable
Result:
marks name     ROW_NUM RANK DENSE_RANK TILE
90    pooja    1       1    1          1
90    saurabh  2       1    1          1
90    paras    3       1    1          1
80    dinesh   4       4    2          1
80    kanchan  5       4    2          2
80    manoj    6       4    2          2
70    harish   7       7    3          2
70    hema     8       7    3          2
60    nitin    9       9    4          3
50    anita    10     10    5          3
50    kamar    11     10    5          3
50    lalit    12     10    5          3

Here:
– ROW_NUM column lists unique ID’s of students, like Roll Numbers.
– RANK lists student rank with equal ranks those secured equal marks, thus there is no 2nd or 3rd.
– DENSE_RANK lists student ranks with no gaps, so here 3 students came 1st &2nd and only 2 3rd.
– NTILE listed students in different but equal groups, can be thought of as different sections.
Now, lets use the partition by clause, its same as group by clause. Lets group/partition the students group by their classes A&B:

select class, marks, stuName,
    row_number() over(partition by class order by marks desc) as [RowNumber],
    rank() over(partition by class order by marks desc) as [Rank],
    dense_rank() over(partition by class order by marks desc) as [DenseRank],
    ntile(3) over(partition by class order by marks desc) as [NTile]
from #tempTable
Result:
class marks name     ROW_NUM RANK DENSE_RANK TILE
A     90    pooja    1       1    1          1
A     90    saurabh  2       1    1          1
A     80    kanchan  3       3    2          2
A     80    manoj    4       3    2          2
A     70    harish   5       5    3          3
A     50    anita    6       6    4          3
B     90    paras    1       1    1          1
B     80    dinesh   2       2    2          1
B     70    hema     3       3    3          2
B     60    nitin    4       4    4          2
B     50    kamar    5       5    5          3
B     50    lalit    6       5    5          3

You can clearly see that our query has grouped students in 2 partitions (classes) and then ranked them.
Simple, isn’t it?

About these ads
  1. August 10, 2010 at 6:52 am

    Great Manoj Bhai u truly rock !

  2. odis
    June 23, 2011 at 5:27 am

    This does not work in DB2, can you offer an alternative?

  1. July 29, 2011 at 9:52 am
  2. April 13, 2012 at 1:38 pm
  3. November 19, 2012 at 12:37 am
  4. June 30, 2014 at 7:58 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 397 other followers

%d bloggers like this: