SQL in a Nutshell, 3rd Edition

Name

RANK

Assigns a rank to each row in a partition, which should be ordered in some manner. The rank for a given row is computed by counting the number of rows preceding the row in question and then adding 1 to the result. Rows with duplicate ORDER BY values will rank the same, leading to gaps in rank numbers.

ANSI SQL Standard Syntax

RANK(  ) OVER {window_name | (window_specification)}

Oracle and SQL Server

Oracle and SQL Server also require the ordering clause and do not allow the framing clause:

RANK(  ) OVER ([partitioning] ordering)

Example

The following Oracle-based example uses the NUM column to rank the rows in the test4 table:

SELECT NUM, RANK(  ) OVER (ORDER BY NUM) rank
FROM test4;
       NUM       RANK
---------- ----------
         0          1
         1          2
         2          3
         3          4
         3          4
         4          6
         5          7

Because both rows where NUM=3 rank the same (at #4), the next-higher row will be ranked at #6. The #5 rank is skipped.