Friday, 17 May 2013

Ranks Fuctions in sql server

Functions In Sql
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

No comments:

Post a Comment

8451831077