SQL in a Nutshell, 3rd Edition

Name

RANK

Computes a rank in a group for a hypothetical row that you supply. This is not a dense rank. If the group contains rows that rank identically, it’s possible for ranks to be skipped. If you want a dense rank, use the DENSE_RANK function.

ANSI SQL Standard Syntax

RANK(value_list) WITHIN GROUP (ORDER BY sort_list)
value_list ::= expression[, expression...]
sort_list ::= sort_item[, sort_item...]
sort_item ::= expression [ASC | DESC] [NULLS FIRST | NULLS LAST]

Items in the value_list correspond by position to items in the sort_list. Therefore, both lists must have the same number of expressions.

Oracle and SQL Server

Oracle and SQL Server follow the ANSI SQL syntax and implement the following analytic syntax:

RANK(  ) OVER ([partitioning] ordering)

For an explanation of the partioning and order clauses, see the section later in this chapter titled “ANSI SQL Window Functions.”

MySQL and PostgreSQL

These platforms do not implement the RANK aggregate function.

Example

The following example determines the rank of the hypothetical new row (num=4, odd=1) within each group of rows from test4, where groups are distinguished by the values in the odd column:

SELECT * FROM test4;
       NUM        ODD
---------- ----------
         0          0
         1          1
         2          0
         3          1
         3          1
         4          0
         5          1

SELECT odd, RANK(4,1) WITHIN GROUP (ORDER BY num, odd)
FROM test4 GROUP BY odd;
       ODD RANK(4,1)WITHINGROUP(ORDERBYNUM,ODD)
---------- ------------------------------------
         0                                    4
         1                                    4

In both cases, the rank of the hypothetical new row is 4. In the group odd=0, the new row ...