About Author SQL Server ArticlesContact US
 
     
   
  SQL Articles
 
See also...

SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER
SQL query to delete duplicate rows
SQL Server CTE(Common Table Expression) and Recursive Queries
CTE Recursive query for data hierarchy(Parent Child hierarchy)
Different methods of SQL queries to insert data in tables
SQL query to display all columns with datatypes for a given Table name
SQL query to check two tables have identical data
SQL query to search a string in database Schema
SQL query to display total number of rows for each table in database
SQL query to search a column name in all tables
SQL server query to search object name in database
SQL Server performance tip - Do not use NOT IN clause in Subquery use LEFT OUTER JOIN instead
SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER


Let's take following sample table and data to know about RANK,RANK_DENSE,NTILE,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 differencec is returns rank with out 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 screeshot, 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 devides 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





 
     
 
Comments
Name  
Email ID
Comment