OLAP ranking function expressions

You can include OLAP ranking function expressions to calculate ordinal ranks that can be applied to each row in the partitioned result set of a query or subquery.

OLAP ranking function expressions are OLAP window expressions that you can include in the Projection list of a SELECT statement, or the ORDER BY clause of a SELECT statement.

Syntax

(1)
OLAP ranking function expressions

1  %OLAP ranking function %OVER clause
for ranking functions
OLAP ranking function

1 
2.1  %LAG function1
2.1  %LEAD function1
2.1  %RANK
function2
2.1  %DENSE_RANK function3
2.1  %PERCENT_RANK
function4
2.1  %CUME_DIST function5
2.1  %NTILE
function6
OVER clause for ranking functions

1  OVER (? %Window
PARTITION clause7 %Window ORDER clause7 )
Notes:

Usage

The ranking values that these functions return are dependent on the window ORDER clause within the OVER clause. The ORDER clause defines the columns or expressions that the database server uses to calculate the ranking values.

If you omit the window PARTITION clause, the scope of the ranking function is the entire result set of the query or subquery, rather than partitioned subsets of results.