# Ranking Functions
# DENSE_RANK ()
Same as that of RANK(). It returns rank without any gaps:
Select Studentid, Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From Exam order by name Studentid Name Subject Marks Rank 101 Ivan Science 80 1 101 Ivan Maths 70 2 101 Ivan Social 60 3 102 Ryan Social 70 1 102 Ryan Maths 60 2 102 Ryan Science 50 3 103 Tanvi Maths 90 1 103 Tanvi Science 90 1 103 Tanvi Social 80 2
A RANK() Returns the rank of each row in the result set of partitioned column.
Select Studentid,Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From Exam order by name,subject Studentid Name Subject Marks Rank 101 Ivan Maths 70 2 101 Ivan Science 80 1 101 Ivan Social 60 3 102 Ryan Maths 60 2 102 Ryan Science 50 3 102 Ryan Social 70 1 103 Tanvi Maths 90 1 103 Tanvi Science 90 1 103 Tanvi Social 80 3
- DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
- RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )
| ||Divides the result set produced by the FROM (opens new window) clause into partitions to which the |
| ||Determines the order in which the |
If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the
DENSE_RANK function do not have gaps and always have consecutive ranks.
The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.
DENSE_RANK is nondeterministic. For more information, see Deterministic and Nondeterministic Functions (opens new window).