Neoview SQL Reference Manual (R2.4 SP2)

RANK Window Function
RANK is a window function that returns the ranking of each row of the current partition specified
by the inline-window-specification. The ranking is relative to the ordering specified in the
inline-window-specification. The return value of RANK starts at 1 for the first row of the window.
Values that are equal have the same rank. The value of RANK advances to the relative position
of the row in the window when the value changes.
RANK() 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 RANK 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.
Examples of RANK Window Function
• Return the rank for each employee based on employee number:
SELECT RANK() OVER (ORDER BY empnum), *
FROM persnl.employee;
• Return the rank for each employee within each department based on salary:
SELECT RANK() OVER (PARTITION BY deptnum ORDER BY salary), *
FROM persnl.employee;
490 OLAP Functions