• Jagan Mohan Reddy Rajidi

SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER

Updated: 4 days ago

Let's take following sample table and data to know about RANK, RANK_DENSE, NTILE and ROW_NUMBER with examples.


Create table ExamResult(name varchar(50),Subject varchar(20),Marks int)

insert into ExamResult values('Adam','Maths',70)
insert into ExamResult values ('Adam','Science',80)
insert into ExamResult values ('Adam','Social',60)

insert into ExamResult values('Rak','Maths',60)
insert into ExamResult values ('Rak','Science',50)
insert into ExamResult values ('Rak','Social',70)

insert into ExamResult values('Sam','Maths',90)
insert into ExamResult values ('Sam','Science',90)
insert into ExamResult values ('Sam','Social',80)

RANK()

Returns the rank of each row in the result set of partitioned column.


select Name,Subject,Marks,
RANK() over(partition by name order by Marks desc)Rank
From ExamResult
order by name,subject


DENSE_RANK()

This is same as RANK() function. Only difference is returns rank without gaps.


select  Name,Subject,Marks,
DENSE_RANK() over(partition by name order by Marks desc)Rank
From ExamResult
order by name



In RANK() result set screenshot, you can notice that there is gap in Rank(2) for the name Sam and same gap is removed in DENSE_RANK().


NTILE()

Distributes the rows in an ordered partition into a specified number of groups.

It divides the partitioned result set into specified number of groups in an order.


Example for NTILE(2)


select Name,Subject,Marks,
NTILE(2) over(partition by name order by Marks desc)Quartile
From ExamResult
order by name,subject


Example for NTILE(3)


select Name,Subject,Marks,
NTILE(3) over(partition by name order by Marks desc)Quartile
From ExamResult
order by name,subject



ROW_NUMBER()

Returns the serial number of the row order by specified column.


select Name,Subject,Marks,
ROW_NUMBER() over(order by Name) RowNumber
From ExamResult
order by name,subject










13 views0 comments

Recent Posts

See All

When you create any new object(table, stored procedure, view,...etc.) in database then same object name will be stored in system table Sysobjects. Following query returns all the object names along wi

Sometimes you get a scenario where you know the column name but don't remember which table it belongs to. Following query will search for a given column name in database schema and returns all objects