SQL in a Nutshell, 2nd Edition

Name

ROW_NUMBER

Synopsis

Assigns a unique number to each row in a partition.

SQL2003 Syntax

ROW_NUMBER( ) OVER ({window_name|(window_specification)}

DB2

DB2 does not allow the framing clause, and it makes the ordering clause optional:

ROW_NUMBER( ) OVER ([partitioning] [ordering])

Oracle

Oracle requires the ordering clause and does 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