OLAP-function
|--+-| ranking-function |-----+---------------------------------|
+-| numbering-function |---+
'-| aggregation-function |-'
ranking-function
|---+-RANK ()-------+--OVER------------------------------------->
'-DENSE_RANK ()-'
>----(--+------------------------------+------------------------>
'-| window-partition-clause |--'
>----| window-order-clause |--)---------------------------------|
numbering-function
|---ROW_NUMBER ()--OVER---(--+------------------------------+--->
'-| window-partition-clause |--'
>----+--------------------------+---)---------------------------|
'-| window-order-clause |--'
aggregation-function
|---column-function--OVER---(--+------------------------------+->
'-| window-partition-clause |--'
>----+--------------------------+------------------------------->
'-| window-order-clause |--'
.-RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING--.
>----+------------------------------------------------------------+---)->
'-| window-aggregation-group-clause |------------------------'
>---------------------------------------------------------------|
window-partition-clause
.-,--------------------------.
V |
|---PARTITION BY-----partitioning-expression---+----------------|
window-order-clause
.-,-------------------------------.
V .-ASC--. |
|---ORDER BY-----sort-key-expression--+------+--+---------------|
'-DESC-'
window-aggregation-group-clause
|---+-ROWS--+---+-| group-start |---+---------------------------|
'-RANGE-' '-| group-between |-'
group-start
|---+-UNBOUNDED PRECEDING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
'-CURRENT ROW-------------------'
group-between
|---BETWEEN--| group-bound1 |--AND--| group-bound2 |------------|
group-bound1
|---+-UNBOUNDED PRECEDING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
+-unsigned-constant--FOLLOWING--+
'-CURRENT ROW-------------------'
group-bound2
|---+-UNBOUNDED FOLLOWING-----------+---------------------------|
+-unsigned-constant--PRECEDING--+
+-unsigned-constant--FOLLOWING--+
'-CURRENT ROW-------------------'
On-Line Analytical Processing (OLAP) functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result. An OLAP function can be included in expressions in a select-list or the ORDER BY clause of a select-statement (SQLSTATE 42903). An OLAP function cannot be used as an argument of a column function (SQLSTATE 42607). The query result to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP function.
When specifying an OLAP function, a window is specified that defines the rows over which the function is applied, and in what order. When used with a column function, the applicable rows can be further refined, relative to the current row, as either a range or a number of rows preceding and following the current row. For example, within a partition by month, an average can be calculated over the previous three month period.
The ranking function computes the ordinal rank of a row within the window. Rows that are not distinct with respect to the ordering within their window are assigned the same rank. The results of ranking may be defined with or without gaps in the numbers resulting from duplicate values.
If RANK is specified, the rank of a row is defined as 1 plus the number of rows that strictly precede the row. Thus, if two or more rows are not distinct with respect to the ordering, then there will be one or more gaps in the sequential rank numbering.
If DENSE_RANK 36 is specified, the rank of a row is defined as 1 plus the number of rows preceding that are distinct with respect to the ordering. Therefore, there will be no gaps in the sequential rank numbering.
The ROW_NUMBER 37 function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order as returned by the subselect (not according to any ORDER BY clause in the select-statement).
The data type of the result of RANK, DENSE_RANK or ROW_NUMBER is BIGINT. The result cannot be null.
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE WHERE SALARY+BONUS > 30000
ORDER BY LASTNAME
Note that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:
ORDER BY RANK_SALARYor
ORDER BY RANK() OVER (ORDER BY SALARY+BONUS DESC)
SELECT WORKDEPT, AVG(SALARY+BONUS) AS AVG_TOTAL_SALARY,
RANK() OVER (ORDER BY AVG(SALARY+BONUS) DESC) AS RANK_AVG_SAL
FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY RANK_AVG_SAL
SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, EDLEVEL
DENSE_RANK() OVER
(PARTITION BY WORKDEPT ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME
SELECT ROW_NUMBER() OVER (ORDER BY WORKDEPT, LASTNAME) AS NUMBER,
LASTNAME, SALARY
FROM EMPLOYEE
ORDER BY WORKDEPT, LASTNAME
SELECT EMPNO, LASTNAME, FIRSTNME, TOTAL_SALARY, RANK_SALARY
FROM (SELECT EMPNO, LASTNAME, FIRSTNME, SALARY+BONUS AS TOTAL_SALARY,
RANK() OVER (ORDER BY SALARY+BONUS DESC) AS RANK_SALARY
FROM EMPLOYEE) AS RANKED_EMPLOYEE
WHERE RANK_SALARY < 6
ORDER BY RANK_SALARY
Notice that a nested table expression was used to first compute the
result, including the rankings, before the rank could be used in the WHERE
clause. A common table expression could also have been used.