Window Functions
Index
Row Number Function
|
ROW_NUMBER |
Rank Functions
|
RANK DENSE_RANK |
PERCENT_RANK CUME_DIST |
Lead or Lag Functions
|
LEAD |
LAG |
Nth Value Functions
|
FIRST_VALUE |
LAST_VALUE |
NTH_VALUE |
Other Window Functions
|
NTILE |
RATIO_TO_REPORT |
Details
Click on the header of the function to switch between railroad diagram and BNF.
Non-standard syntax is marked in green. Compatibility-only non-standard syntax is marked in red, don't use it unless you need it for compatibility with other databases or old versions of H2.
Row Number Function
ROW_NUMBER
ROW_NUMBER ( ) OVER windowNameOrSpecification |
Returns the number of the current row starting with 1. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT ROW_NUMBER() OVER (), * FROM TEST;
SELECT ROW_NUMBER() OVER (ORDER BY ID), * FROM TEST;
SELECT ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
Rank Functions
RANK
RANK ( ) OVER windowNameOrSpecification |
Returns the rank of the current row. The rank of a row is the number of rows that precede this row plus 1. If two or more rows have the same values in ORDER BY columns, these rows get the same rank from the first row with the same values. It means that gaps in ranks are possible. This function requires window order clause. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
See RANK aggregate for a hypothetical set function with the same name.
Example:
SELECT RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
DENSE_RANK
DENSE_RANK ( ) OVER windowNameOrSpecification |
Returns the dense rank of the current row. The rank of a row is the number of groups of rows with the same values in ORDER BY columns that precede group with this row plus 1. If two or more rows have the same values in ORDER BY columns, these rows get the same rank. Gaps in ranks are not possible. This function requires window order clause. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
See DENSE_RANK aggregate for a hypothetical set function with the same name.
Example:
SELECT DENSE_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT DENSE_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
PERCENT_RANK
PERCENT_RANK ( ) OVER windowNameOrSpecification |
Returns the relative rank of the current row. The relative rank is calculated as (RANK - 1) / (NR - 1), where RANK is a rank of the row and NR is a number of rows in window partition with this row. Note that result is always 0 if window order clause is not specified. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
See PERCENT_RANK aggregate for a hypothetical set function with the same name.
Example:
SELECT PERCENT_RANK() OVER (ORDER BY ID), * FROM TEST;
SELECT PERCENT_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
CUME_DIST
CUME_DIST ( ) OVER windowNameOrSpecification |
Returns the relative rank of the current row. The relative rank is calculated as NP / NR where NP is a number of rows that precede the current row or have the same values in ORDER BY columns and NR is a number of rows in window partition with this row. Note that result is always 1 if window order clause is not specified. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
See CUME_DIST aggregate for a hypothetical set function with the same name.
Example:
SELECT CUME_DIST() OVER (ORDER BY ID), * FROM TEST;
SELECT CUME_DIST() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
Lead or Lag Functions
LEAD
Returns the value in a next row with specified offset relative to the current row. Offset must be non-negative. If IGNORE NULLS is specified rows with null values in selected expression are skipped. If number of considered rows is less than specified relative number this function returns NULL or the specified default value, if any. If offset is 0 the value from the current row is returned unconditionally. This function requires window order clause. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT LEAD(X) OVER (ORDER BY ID), * FROM TEST;
SELECT LEAD(X, 2, 0) IGNORE NULLS OVER (
PARTITION BY CATEGORY ORDER BY ID
), * FROM TEST;
LAG
Returns the value in a previous row with specified offset relative to the current row. Offset must be non-negative. If IGNORE NULLS is specified rows with null values in selected expression are skipped. If number of considered rows is less than specified relative number this function returns NULL or the specified default value, if any. If offset is 0 the value from the current row is returned unconditionally. This function requires window order clause. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT LAG(X) OVER (ORDER BY ID), * FROM TEST;
SELECT LAG(X, 2, 0) IGNORE NULLS OVER (
PARTITION BY CATEGORY ORDER BY ID
), * FROM TEST;
Nth Value Functions
FIRST_VALUE
Returns the first value in a window. If IGNORE NULLS is specified null values are skipped and the function returns first non-null value, if any.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT FIRST_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
SELECT FIRST_VALUE(X) IGNORE NULLS OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
LAST_VALUE
Returns the last value in a window. If IGNORE NULLS is specified null values are skipped and the function returns last non-null value before them, if any; if there is no non-null value it returns NULL. Note that the last value is actually a value in the current group of rows if window order clause is specified and window frame clause is not specified.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT LAST_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
SELECT LAST_VALUE(X) IGNORE NULLS OVER (
PARTITION BY CATEGORY ORDER BY ID
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
), * FROM TEST;
NTH_VALUE
Returns the value in a row with a specified relative number in a window. Relative row number must be positive. If FROM LAST is specified rows a counted backwards from the last row. If IGNORE NULLS is specified rows with null values in selected expression are skipped. If number of considered rows is less than specified relative number this function returns NULL. Note that the last row is actually a last row in the current group of rows if window order clause is specified and window frame clause is not specified.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT NTH_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
SELECT NTH_VALUE(X) IGNORE NULLS OVER (
PARTITION BY CATEGORY ORDER BY ID
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
), * FROM TEST;
Other Window Functions
NTILE
NTILE ( long ) OVER windowNameOrSpecification |
Distributes the rows into a specified number of groups. Number of groups should be a positive long value. NTILE returns the 1-based number of the group to which the current row belongs. First groups will have more rows if number of rows is not divisible by number of groups. For example, if 5 rows are distributed into 2 groups this function returns 1 for the first 3 row and 2 for the last 2 rows. This function requires window order clause. Window frame clause is not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT NTILE(10) OVER (ORDER BY ID), * FROM TEST;
SELECT NTILE(5) OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
RATIO_TO_REPORT
Returns the ratio of a value to the sum of all values. If argument is NULL or sum of all values is 0, then the value of function is NULL. Window ordering and window frame clauses are not allowed for this function.
Window functions in H2 may require a lot of memory for large queries.
Example:
SELECT X, RATIO_TO_REPORT(X) OVER (PARTITION BY CATEGORY), CATEGORY FROM TEST;