SQL Reference Guide

Ranking

A window function operates on a group of rows from a result set. The rows are grouped together based on the OVER clause. Unlike an aggregate function, window functions do not group rows into a single output, the rows remain separate rows however they include an window function value that is determined by values from all the rows in the group.

The PARTITION BY clause splits the result set into partitions over which the window function operates. When moving to a new partition the window function resets, and values from other partitions are not considered when calculating window function values. The ORDER BY clause orders the rows within a particular partition. The ordering also resets when moving to a new partition.Ranking functions are nondeterministic as they are computed for each generated result set.

• Aggregate functions and window functions can now be used together in the select clause of a query, e.g. select avg(fld), row_number() over (order by fld) from tbl
• Window functions can be used in the ORDER BY clause, e.g. select fld from tbl order by row_number () over (order by fld);

In This Section DENSE_RANK windowing function MAX windowing function MIN windowing function NTH_VALUE windowing function NTILE windowing function RANK windowing function ROW_NUMBER windowing function