Neoview SQL Reference Manual (R2.5)

ROW_NUMBER Window Function
ROW_NUMBER is a window function that returns the row number of each row of the current
window specified by the inline-window-specification.
ROW_NUMBER () OVER (inline-window-specification)
inline-window-specification is:
[PARTITION BY expression [, expression]...]
[ORDER BY expression [ASC[ENDING] | DESC[ENDING]]
[,expression [ASC[ENDING] | DESC[ENDING]]]...]
inline-window-specification
specifies the window over which the ROW_NUMBER is computed. The
inline-window-specification can contain an optional PARTITION BY clause and an
optional ORDER BY clause. The PARTITION BY clause specifies how the intermediate result
is partitioned and the ORDER BY clause specifies how the rows are ordered within each
partition.
window-frame-clause
specifies the window within the partition over which the ROW_NUMBER is computed.
Examples of ROW_NUMBER Window Function
• Return the row number for each row of the employee table:
SELECT ROW_NUMBER () OVER(ORDER BY empnum), *
FROM persnl.employee;
• Return the row number for each row within each department:
SELECT ROW_NUMBER () OVER(PARTITION BY deptnum ORDER BY empnum), *
FROM persnl.employee;
ROW_NUMBER Window Function 503