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
-----------------------------------------
