DevTut

# 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

# RANK()

A RANK() Returns the rank of each row in the result set of partitioned column.

Eg :

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

# Syntax

  • DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )
  • RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

# Parameters

Arguments Details
<partition_by_clause> Divides the result set produced by the FROM (opens new window) clause into partitions to which the DENSE_RANK function is applied. For the PARTITION BY syntax, see OVER Clause (Transact-SQL) (opens new window).
<order_by_clause> Determines the order in which the DENSE_RANK function is applied to the rows in a partition.
OVER ( [ partition_by_clause ] order_by_clause) partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the function is applied. The order_by_clause is required. The <rows or range clause> of the OVER clause cannot be specified for the RANK function. For more information, see OVER Clause (Transact-SQL) (opens new window).

# Remarks

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).

← Window functions →