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
)
-------------------------
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)
------------------
use AdventureWorks
select Name,mark,Dept,ROW_NUMBER() over (order by mark asc) as RowNumber
from Rank_demo
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.
select Name,mark,Dept,RANK() over (order by mark asc) as 'Rank'
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.
select Name,mark,Dept,DENSE_RANK() over (order by mark asc) as 'Dense_Rank'
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.
select Name,mark,Dept,NTILE(4) over (partition by Dept order by mark asc) as 'NTILE'
Here I did partition based on dept column.
I hope my article will be helpful for beginners like me.
Naomi N edited Revision 3. Comment: Added See Also
Naomi N edited Revision 2. Comment: Minor edit
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.