SQL in a Nutshell, 3rd Edition

Name

ROW_NUMBER

Assigns a unique number to each row in a partition.

ANSI SQL Standard Syntax

ROW_NUMBER(  ) 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:

ROW_NUMBER(  ) OVER ([partitioning] ordering)

Example

SELECT NUM, ODD, ROW_NUMBER(  ) OVER
 (PARTITION BY ODD ORDER BY NUM) cumedist
FROM test4;
       NUM        ODD   CUMEDIST
---------- ---------- ----------
         0          0          1
         2          0          2
         4          0          3
         1          1          1
         3          1          2
         3          1          3
         5          1          4