OLAP ranking function expressions

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

OLAP ranking function expressions

|--| OLAP ranking function |--| OVER clause for ranking functions |--|

OLAP ranking function

                      (1)              
|--+-| LAG function |--------------+----------------------------|
   |                   (1)         |   
   +-| LEAD function |-------------+   
   |                   (2)         |   
   +-| RANK function |-------------+   
   |                         (3)   |   
   +-| DENSE_RANK function |-------+   
   |                           (4) |   
   +-| PERCENT_RANK function |-----+   
   |                        (5)    |   
   +-| CUME_DIST function |--------+   
   |                    (6)        |   
   '-| NTILE function |------------'   

OVER clause for ranking functions

                                                                         (7)      
|--OVER--(--+---------------------------------+--| Window ORDER clause |------)--|
            |                             (7) |                                   
            '-| Window PARTITION clause |-----'                                   

1. See LAG and LEAD functions
2. See RANK function
3. See DENSE_RANK function
4. See PERCENT_RANK function
5. See CUME_DIST function
6. See NTILE function
7. See OVER clause for OLAP window expressions

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.