▲
Search:
| Highlight keyword(s)
Home Download Cheat Sheet Documentation Quickstart Installation Tutorial Features Security Performance Advanced Reference Commands Functions • Aggregate • Window Data Types SQL Grammar System Tables Javadoc PDF (2 MB) Support FAQ Error Analyzer Google Group Appendix History License Build Links MVStore Architecture Migration to 2.0 |  | # 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
`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
`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
`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
`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
`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
    LEAD(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`LEAD ( value` |
`, offsetInt` |
`, defaultValue`
| `)` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
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
    LAG(value [, offsetInt [, defaultValue]]) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`LAG ( value` |
`, offsetInt` |
`, defaultValue`
| `)` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
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
    FIRST_VALUE(value) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`FIRST_VALUE ( value )` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
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
    LAST_VALUE(value) [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`LAST_VALUE ( value )` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
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
    NTH_VALUE(value, nInt) [FROM {FIRST|LAST}] [{RESPECT|IGNORE} NULLS]
    OVER windowNameOrSpecification
`NTH_VALUE ( value , nInt )` |
`FROM` |
| `FIRST` |
| `LAST` |
| `RESPECT` |
| `IGNORE` |
| `NULLS`
`OVER windowNameOrSpecification`
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
`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
    RATIO_TO_REPORT(value)
    OVER windowNameOrSpecification
`RATIO_TO_REPORT ( value )`
`OVER windowNameOrSpecification`
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;