SQL RANK Function Example

14. SQL RANK Function Example

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

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

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

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


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

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

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

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

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