Ranking Functions (Row_Number, Rank, Dense_Rank and NTILE)

Ranking Functions (Row_Number, Rank, Dense_Rank and NTILE)

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  

 

  

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 3. Comment: Added See Also

  • Naomi  N edited Revision 2. Comment: Minor edit

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Revision 2. Comment: Minor edit

  • Naomi  N edited Revision 3. Comment: Added See Also

  • Thanks Naomi for your minor in edit in article.

  • Selvakumar, please check the image place holders in your article, some images seem to be missing.

Page 1 of 1 (4 items)