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