Nth Value (NTH_VALUE) of Analytic Functions in Oracle SQL
NTH_VALUE
NTH_VALUE function is fetch the nth value from set of data. N is the number of rows.
Syntax:
NTH_VALUE (measure_expr/Column, n)
[ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ]
OVER (analytic_clause)
Note:
{RESPECT | IGNORE} NULLS describe that null values of measure_expr/column are included or eliminated.
n represents the nth row
FROM {FIRST | LAST} describe whether the calculation begins at the first or last row of the window.
Examples of NTH_VALUE function:
Fetch the 4th lowest position of salary from employees table
SELECT employee_id,
salary,
NTH_VALUE(salary,4) OVER (ORDER BY salary) AS forth_sal
FROM employees;
EMPLOYEE_ID SALARY FORTH_SAL ----------- ---------- ---------- 132 2100 128 2200 136 2200 127 2400 2400 135 2400 2400 119 2500 2400
Use of First or LAST clauses with Nth_value function
It clear in following example:
FIRST nth row result pick column/expression value as offset row as output only. It not changed value.
LAST nth row result pick column/expression value as n-offset row as output. It changed value regularly as rows fetch.
offset is the value you defined in nth_value function for nth row.
SELECT employee_id,
department_id,
salary,
NTH_VALUE(salary, 3) FROM FIRST OVER (ORDER BY salary) AS third_lowest_sal,
NTH_VALUE(salary, 3) FROM LAST OVER (ORDER BY salary) AS third_highest_sal
FROM employees;
EMPLOYEE_ID DEPARTMENT_ID SALARY THIRD_LOWEST_SAL THIRD_HIGHEST_SAL ----------- ------------- ------ ---------------- ----------------- 132 50 2100 128 50 2200 2200 2100 136 50 2200 2200 2100 127 50 2400 2200 2200 135 50 2400 2200 2200 119 30 2500 2200 2500 131 50 2500 2200 2500 140 50 2500 2200 2500 144 50 2500 2200 2500 182 50 2500 2200 2500 191 50 2500 2200 2500
Find Nth lowest value or Nth highest value
Fetch data for 3rd position 3rd lowest salary or 3rd highest salary.
SELECT employee_id,
salary,
NTH_VALUE(salary, 3) OVER (ORDER BY salary) AS third_lowest_sal,
NTH_VALUE(salary, 3) OVER (ORDER BY salary desc) AS third_highest_sal
FROM employees order by salary;
EMPLOYEE_ID SALARY THIRD_LOWEST_SAL THIRD_HIGHEST_SAL ----------- ------ ---------------- ----------------- 132 2100 17000 128 2200 2200 17000 136 2200 2200 17000 135 2400 2200 17000 127 2400 2200 17000 119 2500 2200 17000 -----------------------------------------