NTH_VALUE
Purpose
The NTH_VALUE function returns the value of expr of the nth row in the window specified by the over_clause.
Syntax
nth_value::=
Usage Notes
- NTH_VALUE is only available as an analytic function (i.e., in combination with an over_clause). For more information, refer to Analytic Functions section.
- The default value of the FROM configuration is FROM FIRST.
- The default value of the null treatment is RESPECT NULLS.
- The value of n has to be NULL or a DECIMAL value greater than 0.
- If the value of n is NULL, the result of the function is NULL.
- If the value of n is less than or equal to 0, Exasol returns an error.
- If the value of n is greater than the number of rows in the window, the result of NTH_VALUE is NULL.
Example
Analytic Function
SELECT
id, department, hire_date,
NTH_VALUE(id, 3) FROM LAST RESPECT NULLS
OVER (PARTITION BY department ORDER BY hire_date) NTH_VAL
FROM employee_table ORDER BY department, hire_date;
Result
ID | DEPARTMENT | HIRE_DATE | NTH_VAL |
2005 | ACCOUNTS | 2013-01-01 | NULL |
2003 | ACCOUNTS | 2015-07-01 | NULL |
2002 | ACCOUNTS | 2017-01-01 | 2003 |
2004 | ACCOUNTS | 2017-01-01 | 2003 |
2001 | ACCOUNTS | 2018-07-01 | 2002 |
1003 | HR | 2014-01-01 | NULL |
1002 | HR | 2016-01-01 | 1003 |
1004 | HR | 2016-01-01 | 1003 |
1001 | HR | 2018-01-01 | 1002 |