Neoview SQL Reference Manual (R2.3)

SELECT empnum, MIN(salary)
OVER (ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the running minimum of the SALARY column within each department:
SELECT deptnum, empnum, MIN(salary)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS UNBOUNDED PRECEDING)
FROM persnl.employee;
• Return the moving minimum of salary within each department over a window of the last
4 rows:
SELECT deptnum, empnum, MIN(salary)
OVER (PARTITION BY deptnum ORDER BY empnum ROWS 3 PRECEDING)
FROM persnl.employee;
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 {colname} [ASC[ENDING] | DESC[ENDING]]
[,{colname} [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;
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 {colname} [ASC[ENDING] | DESC[ENDING]]
[,{colname} [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
452 OLAP Functions