SQL Server RANK Function

Updated 2023-10-24 13:44:21.390000

Description

Use the aggregate function RANK to return the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.

Syntax

SELECT [westclintech].[wct].[RANK] (
  <@number, float,> 
 ,<@x, float,> <@order, int,>)

Arguments

@number

is the number whose rank you want to find. @number is an expression of type float or of a type that can be implicitly converted to float.

@x

the values to be used in the RANK calculation. @x is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

If @number is not contained in the dataset, RANK returns an error.

@number must remain invariant for the GROUP.

@order must remain invariant for the group.

RANK is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

Examples

To ascertain where the number 2 ranks, from highest to lowest

SELECT wct.RANK(   2, --@number
                   x, --@x
                   0  --@order
               ) as RANK
FROM
(
    VALUES
        (1),
        (2),
        (2),
        (2),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (8),
        (8),
        (8),
        (9),
        (10),
        (11),
        (12),
        (13),
        (13),
        (14)
) n (x);

This produces the following result.

RANK
17

Using the same data, with ranking from lowest to highest.

SELECT wct.RANK(2, x, 1) as RANK
FROM
(
    VALUES
        (1),
        (2),
        (2),
        (2),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (8),
        (8),
        (8),
        (9),
        (10),
        (11),
        (12),
        (13),
        (13),
        (14)
) n (x);