Ranking Functions (Row_Number, Rank, Dense_Rank and NTILE)

  • Article

In this article we will discuss very simple and useful information in our daily SQL development work.

SQL server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK and NTILE that are referred as Rank functions. Ranking functions return a ranking value for each row in a table. Ranking functions are non deterministic.

Four functions return rank value but each function has different properties.

Here we will explain the difference:

ROW_NUMBER ()

   Return sequential number of the row from 1 to N.

 ----Create Demo Table
use AdventureWorks

go

Create table Rank_demo

(

  Name varchar(20),

  Dept varchar(3),

  mark Int

)

go

insert into Rank_demo values('Selva','CSE',60)

insert into Rank_demo values('John','CSE',70)

insert into Rank_demo values('Smith','CSE',60)

insert into Rank_demo values('Albert','ECE',80)

insert into Rank_demo values('kevin','ECE',80)

insert into Rank_demo values('Paul','IT',80)

insert into Rank_demo values('Peter','IT',90)

go

use AdventureWorks

go

select Name,mark,Dept,ROW_NUMBER() over (order by mark asc) as RowNumber

from Rank_demo

go

 

 ROW_NUMBER () function return sequential number for rows in the table

**RANK ()
**
Returns rank for each row. Like how we struggled in school life. Returned rank is based on partition clause.

  

use AdventureWorks

go

select Name,mark,Dept,RANK() over (order by mark asc) as 'Rank'

from Rank_demo

go

 

DENSE_RANK ()

  Returns Rank in sequential order based on partition clause. It won’t skip rank like RANK () function. Dense_Rank () function is useful to get ‘N’th highest or lowest value in the table more accurate compared to RANK () function.

 

use AdventureWorks

go

select Name,mark,Dept,DENSE_RANK() over (order by mark asc) as 'Dense_Rank'

from Rank_demo

go

 

NTILE ()

   It divides result set into equal number of groups based on the partition clause. NTILE is used to split the result set in to groups.

 use AdventureWorks

go

select Name,mark,Dept,NTILE(4) over (partition by Dept order by mark asc) as 'NTILE'

from Rank_demo

go

 

Here I did partition  based on dept column.

 

I hope my article will be helpful for beginners like me.

See Also

T-SQL Window Functions - Part 2: Ranking Functions