NTH_VALUE

Purpose

The NTH_VALUE function returns the value of expr of the nth row in the window specified by the over_clause.

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