Ranking functions

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);

DENSE_RANK windowing function

Syntax

DENSE_RANK() OVER ( [ PARTITION BY value_expression, ... [n] ] [ order_by_clause ] )

 

Requires PARTITION BY and/or order_by_clause.

Description

DENSE_RANK returns a temporary value calculated when the query is run. It returns the rank of a row within a partition of a result set. The rank is one plus the number of ranks that come before the row in question with no gaps in the ranking values.

Example

Given the following table definition:

create table ranking (c1 int, c2 int);

insert into ranking values (1,1);

insert into ranking values (1,2);

insert into ranking values (1,2);

insert into ranking values (1,3);

insert into ranking values (2,1);

insert into ranking values (2,1);

insert into ranking values (2,2);

insert into ranking values (2,3);

commit work;

 

select c1, c2, dense_rank() over (partition by c1 order by c2) from ranking;



C1 C2 DENSE_RANK()

-- -- ------------

1 1 1

1 2 2

1 2 2

1 3 3

2 1 1

2 1 1

2 2 2

2 3 3

 

Notes

  • DENSE_RANK ensures consecutive values for all result sets with no gaps in numbering. Use DENSE_RANK for "tied" rank numbering.
  • The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
  • Only a single ranking function may be used in a statement.
  • Ranking functions are nondeterministic as they are computed for each generated result set.
  • Introduced in V12.0.1

See Also

MAX windowing function

Syntax

MAX ( { [ALL] expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

 

Requires partition_by_clause and/or order_by_clause.

PARTITION BY

Description

The aggregate function MAX returns the maximum of the values in a group. The argument column_ref or expression can be of any type. The result of the function is of the same data type as that of the argument. The result can have a null value.

Example

select empno, deptno, projno, sal, max(sal) over (partition by deptno, projno order by projno) max_proj_sal from emp;

 

EMPNO DEPTNO PROJNO SAL MAX_PROJ_SAL

----- ------ ------ --- -----------

7782 10 101 2450.00 2450.000000

7839 10 102 5000.00 5000.000000

7934 10 102 1300.00 5000.000000

7566 20 101 2975.00 3000.000000

7329 20 101 800.00 3000.000000

7876 20 101 1100.00 3000.000000

7788 20 101 3000.00 3000.000000

7902 20 101 3000.00 3000.000000

7698 30 101 2850.00 2850.000000

7844 30 102 1500.00 1500.000000

7900 30 102 950.00 1500.000000

7521 30 103 1250.00 1600.000000

7654 30 103 1250.00 1600.000000

7499 30 103 1600.00 1600.000000

See Also

MIN windowing function

Syntax

MIN ( { [ALL] expression } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

 

Requires partition_by_clause and/or order_by_clause.

Description

The aggregate function MIN returns the minimum of the values in a group. The argument column_ref or expression can be of any type. The result of the function is of the same data type as that of the argument. The result can have a null value.

Example

select empno, deptno, projno, sal, min(sal) over (partition by deptno, projno order by projno) min_proj_sal from emp;

 

EMPNO DEPTNO PROJNO SAL MIN_PROJ_SAL

----- ------ ------ --- -----------

7782 10 101 2450.00 2450.000000

7839 10 102 5000.00 1300.000000

7934 10 102 1300.00 1300.000000

7566 20 101 2975.00 800.000000

7329 20 101 800.00 800.000000

7876 20 101 1100.00 800.000000

7788 20 101 3000.00 800.000000

7902 20 101 3000.00 800.000000

7698 30 101 2850.00 2850.000000

7844 30 102 1500.00 950.000000

7900 30 102 950.00 950.000000

7521 30 103 1250.00 1250.000000

7654 30 103 1250.00 1250.000000

7499 30 103 1600.00 1250.000000

See Also

NTH_VALUE windowing function

Declaration

NTH_VALUE ( { expression, offset } ) OVER ( [ partition_by_clause ] [ order_by_clause ] )

 

Requires partition_by_clause and/or order_by_clause.

Description

The windowing function NTH_VALUE returns the value of the expression at the row specified by the offset within the window frame.

  • The argument expression can be of any type.
  • The expression must return a single value.
  • The argument offset is a positive integer that specifies the row number relative to the first row in the window.

The result type of NTH_VALUE matches the type of the argument expression. If the offset specifies a row outside the range of the window, NTH_VALUE returns NULL.

Example

select c1, c2, c3, nth_value(c2, 2) over (partition by c3 order by c2) from nth;


C1 C2 C3 NTH_VALUE(C2

-- -- -- ------------

1 1 1

2 2 1 2

3 2 2

4 3 2 3

5 3 3

5 records selected

NTILE windowing function

Syntax

NTILE( integer_expression ) OVER ( [ PARTITION BY value_expression, ... [n] ] [ order_by_clause ] )

 

Requires PARTITION BY and/or order_by_clause.

Description

NTILE distributes the rows in an ordered partition into a specified integer_expression number of numbered groups. Group numbering starts at one. For each row, NTILE returns the number of the group the row belongs to. If the number of rows does not divide evenly into the number of groups specified, the beginning groups will have one more row in them than the ending groups such that the full number of rows is divided among the groups.

Example

Given this table definition.

create table ranking (c1 int, c2 int);

insert into ranking values (1,1);

insert into ranking values (1,2);

insert into ranking values (1,2);

insert into ranking values (1,3);

insert into ranking values (2,1);

insert into ranking values (2,1);

insert into ranking values (2,2);

insert into ranking values (2,3);

commit work;


select c1, c2, ntile(3) over (partition by c1 order by c2) from ranking;



C1 C2 NTILE(3.000000)

-- -- --------------

1 1 1

1 2 1

1 2 2

1 3 3

2 1 1

2 1 1

2 2 2

2 3 3

Notes

  • The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
  • Only a single ranking function may be used in a statement.
  • Ranking functions are nondeterministic as they are computed for each generated result set.
  • Introduced in V12.0.1

See Also

RANK windowing function

Syntax

RANK() OVER ( [ PARTITION BY value_expression, ... [n] ] [ order_by_clause ] )

 

Requires PARTITION BY and/or order_by_clause.

Description

RANK returns a temporary value calculated when the query is run. It returns the rank of a row within a partition of a result set. The rank is one plus the number of ranks that come before the row in question. When multiple rows share the same rank, the rank of the next row is not consecutive.

Example

Given the following table definition:

create table ranking (c1 int, c2 int);

insert into ranking values (1,1);

insert into ranking values (1,2);

insert into ranking values (1,2);

insert into ranking values (1,3);

insert into ranking values (2,1);

insert into ranking values (2,1);

insert into ranking values (2,2);

insert into ranking values (2,3);

commit work;

 

select c1, c2, rank() over (partition by c1 order by c2) from ranking;



C1 C2 RANK()

-- -- -----

1 1 1

1 2 2

1 2 2

1 3 4

2 1 1

2 1 1

2 2 3

2 3 4

Notes

  • RANK value is not consecutive for all result sets and can produce gaps in numbering. Use DENSE_RANK for absolute rank numbering.
  • The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
  • Only a single ranking function may be used in a statement.
  • Ranking functions are nondeterministic as they are computed for each generated result set.
  • Introduced in V12.0.1

See Also

ROW_NUMBER windowing function

Syntax

ROW_NUMBER() over ( [ PARTITION BY value_expression, ... [n] ] [ order_by_clause ] )

 

Requires PARTITION BY and/or order_by_clause.

Description

ROW_NUMBER is a member of a collection of ranking functions and returns a temporary value calculated when the query is run. It returns the sequential number of a row within a partition of a result set. The values start with 1 for the first row in each partition.

Example

Given this table definition.

create table ranking (c1 int, c2 int);

insert into ranking values (1,1);

insert into ranking values (1,2);

insert into ranking values (1,2);

insert into ranking values (1,3);

insert into ranking values (2,1);

insert into ranking values (2,1);

insert into ranking values (2,2);

insert into ranking values (2,3);

commit work;


ROW_NUMBER() over ( [PARTITION BY value_expression, ... [n]] order_by_clause;


select c1, c2, row_number() over (partition by c1 order by c2) from ranking;

C1 C2 ROW_NUMBER()

-- -- ------------

1 1 1

1 2 2

1 2 3

1 3 4

2 1 1

2 1 2

2 2 3

2 3 4

Notes

  • The PARTITION BY clause distributes the rows in the result set into partitions by one or more value_expression.
  • Only a single ranking function may be used in a statement.
  • Ranking functions are nondeterministic as they are computed for each generated result set.
  • Introduced in V12.0.1

See Also

scoring evaluation

Was this article helpful?

Give feedback about this article

Related Articles