Ranks Fuctions in sql server

Ranks Fuctions in sql server

                       Many inbuild functions are provided by sql sever ,I can explain few of those. in this article
I can explain how to use  RANK(), DENSE_RANK(), NTILE (), and row_number() functions in sql server

for your better understanding I explain with examples .

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

Insert into #ExamResult values('Sandip','Maths',70)
Insert into #ExamResult values ('sandip','Science',80)
Insert into #ExamResult values ('sandip','Social',60)

Insert into #ExamResult values('Harshal','Maths',60)
Insert into #ExamResult values ('Harshal','Science',50)
Insert into #ExamResult values ('Harshal','Social',70)

Insert into #ExamResult values('Ram','Maths',90)
Insert into #ExamResult values ('Ram','Science',90)
Insert into #ExamResult values ('Ram','Social',90)
Insert into #ExamResult values ('Ram','Social',80)

1 ) RANK ()

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in
each partition.

1) Syntax :

RANK () OVER ([partition_by_clause] order_by_clause)

A below Query gives you output rank with a Partition by names

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

A below Query gives you output rank without a Partition

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

2) DENSE_RANK()

Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

1) Syntax :

DENSE_RANK() OVER ([partition_by_clause] order_by_clause)

A below Query gives you output DENSE_RANK() with a Partition by names

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

A below Query gives you output DENSE_RANK() without a Partition

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

3) ROW_NUMBER()

1) Syntax :

row_number() OVER ([partition_by_clause] order_by_clause)

below Query gives you output row_number() with a Partition by names

Returns the sequential number of a row within a partition of a result set,
starting at 1 for the first row in each partition.

select Name,Subject,Marks,
row_number() over (partition by name order by Marks desc ) rowid
From #ExamResult
order by name,subject

A below Query gives you output ROW_NUMBER() without a Partition

Returns the sequential number of a row of a result set

select Name,Subject,Marks,
row_number() over ( order by Marks desc ) rowid
From #ExamResult
order by name,subject

4) NTILE()

Syntax :

NTILE (integer_expression) OVER ([partition_by_clause] order_by_clause)

A below Query gives you output with a Partition by names and integer_expression=2

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

A below Query gives you output withOut a Partition by names and integer_expression=2

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

A below Query gives you output withOut a Partition by names and integer_expression=3

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