On-Line Analytical Processing (OLAP) specifications provide the ability to return ranking and row numbering as a scalar value in a query result.
OLAP-specification |--+-ordered-OLAP-specification-+-------------------------------| '-numbering-specification----' ordered-OLAP-specification |--+-RANK-------+--(--)--OVER--(--+-------------------------+--window-order-clause--)--| '-DENSE_RANK-' '-window-partition-clause-' numbering-specification |--ROW_NUMBER--(--)--OVER--(--+-------------------------+--+---------------------+--)--| '-window-partition-clause-' '-window-order-clause-' window-partition-clause .-,-----------------------. V | |--PARTITION BY----partitioning-expression-+--------------------| window-order-clause |--ORDER BY-----------------------------------------------------> .-,--------------------------------------------------. | .-NULLS LAST-. | V .-ASC--+------------+---. | >----+-sort-key-expression--+-----------------------+-+-+-------| | +-ASC NULLS FIRST-------+ | | | .-NULLS FIRST-. | | | +-DESC--+-------------+-+ | | '-DESC NULLS LAST-------' | '-ORDER OF--table-designator---------------------'
An OLAP specification can be included in an expression in a select-list or the ORDER BY clause of a select-statement. The query result to which the OLAP specification is applied is the result table of the innermost subselect that includes the OLAP specification. OLAP specifications are sometimes referred to as window functions.
An OLAP specification is not valid in a WHERE, VALUES, GROUP BY, HAVING, or SET clause, or join-condition in an ON clause of a joined table. An OLAP specification cannot be used as an argument of an aggregate function in the select-list.
When invoking an OLAP specification, a window is specified that defines the rows over which the function is applied, and in what order.
The data type of the result of RANK, DENSE_RANK, or ROW_NUMBER is BIGINT. The result cannot be null.
a column of the result table
of the subselect that contains the OLAP specification.
A partitioning-expression cannot
include a scalar-fullselect or any function
that is not deterministic or has an external action.
The sum of the length attributes of the sort-key-expressions must
not exceed 32766-n bytes (where n is the number of sort-key-expressions specified
that allow nulls).
Partitioning and ordering are performed in accordance with the comparison rules described in Assignments and comparisons.
If a collating sequence other than *HEX is in effect when the statement that contains the OLAP expression is executed, and the partitioning-expressions or the sort-key-expressions are SBCS data, mixed data, or Unicode data, then the results are determined using the weighted values. The weighted values are derived by applying the collating sequence to the partitioning-expressions and the sort-key-expressions.
An OLAP specification is not allowed if the query specifies:
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 LASTNAMENote that if the result is to be ordered by the ranking, then replace ORDER BY LASTNAME with:
ORDER BY RANK_SALARY
or: 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
Note 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.