Nth Value (NTH_VALUE) of Analytic Functions in Oracle SQL

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.