This article is written about how to use the Oracle/PLSQL NTH_VALUE function with syntax and examples.

## Description

The Oracle/PLSQL NTH_VALUE function returns the nth value in an ordered set of values from an analytic window. It is similar to the FIRST_VALUE and LAST_VALUE functions except the NTH_VALUE lets you find a specific position in the analytic window such as 2nd, 3rd, or 4th value.

## Syntax

The syntax for the NTH_VALUE function in Oracle/PLSQL is:

```
NTH_VALUE (measure_column, n)
[FROM FIRST | FROM LAST]
[RESPECT NULLS | IGNORE NULLS]
OVER ([query_partition_clause] [order_by_clause [windowing_clause]])
```

Parameters or Arguments

measure_column

The column or expression that you wish to return.

n

It is the nth value of measure_column in the analytic window that you wish to return.

FROM FIRST | FROM LAST

Optional. It determines whether the calculation starts at the first row of the analytic window or the last row of the analytic window. If this parameter is omitted, the default is FROM FIRST which starts at the first row.

RESPECT NULLS | IGNORE NULLS

Optional. It determines whether NULL values are included or ignored in the analytic window calculation. If this parameter is omitted, the default is RESPECT NULLS which includes NULL values.

query_partition_clause

Optional. It is used to partition the results into groups based on one or more expressions.

order_by_clause

Optional. It is used to order the data within each partition.

windowing_clause

Optional. It determines the rows in the analytic window to evaluate and it is important that you use the correct windowing_clause or you could get unexpected results. It can be a value such as:

windowing_clause Description

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Last row in the window changes as the current row changes (default)

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING First row in the window changes as the current row changes

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING All rows are included in the window, regardless of the current row

## Returns

The NTH_VALUE function returns the nth value in an ordered set of values from an analytic window.

If there are less than n rows in the data source window, the NTH_VALUE function will return NULL.

If the parameter n is NULL, the NTH_VALUE function returns an error.

## Applies To

The NTH_VALUE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g Release 2

## DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

## Example

Let’s look at some Oracle NTH_VALUE function examples and explore how to use the NTH_VALUE function in Oracle/PLSQL.

Second Highest Value by Dept_id

In this NTH_VALUE example, we have a table called employees with the following data:

```
EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY DEPT_ID
----------- --------- ---------- ------ -------
1000 Jackson Joe 2000 10
2000 Smith Jane 3500 10
3000 Ferguson Samantha 1900 10
4000 Reynolds Allen 4000 20
5000 Anderson Paige 3250 20
6000 Johnson Derek 2750 20
7000 Nelson Sarah 5000 30
8000 Burke Russell 1500 30
```

Now let’s demonstrate how the NTH_VALUE function works by selecting data from the employees table. To return the dept_id and the second highest salary for dept_id 10 and 20, enter the following SQL statement in Oracle:

```
SELECT DISTINCT dept_id, NTH_VALUE(salary,2)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "SECOND HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;
```

These are the results that you should see:

```
DEPT_ID SECOND HIGHEST
------- --------------
10 2000
20 3250
```

In this example, the NTH_VALUE returns the 2nd salary value as specified by NTH_VALUE(salary,2). The analytic window will partition results by dept_id and order the data by salary in descending order as specified by PARTITION BY dept_id ORDER BY salary DESC. The RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING windowing_clause is used to ensure that all rows are included regardless of the current row. If you did not include this windowing_clause in this example, you would get some unexpected results.

Second and Third Highest Salaries by Dept_id

Now, let’s show you how to use the NTH_VALUE function to return the 2nd and 3rd highest salaries for dept_id 10 and 20.

Based on the same employees table, enter the following SQL statement:

```
SELECT DISTINCT dept_id, NTH_VALUE(salary,2)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "SECOND HIGHEST",
NTH_VALUE(salary,3)
OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "THIRD HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER BY dept_id;
```

These are the results that you should see:

```
DEPT_ID SECOND HIGHEST THIRD HIGHEST
------- -------------- -------------
10 2000 1900
20 3250 2750
```

In this example, we have used the NTH_VALUE function multiple times to find different nth values in the employees table.

Second Lowest Salary for all Employees

Finally, let’s use the NTH_VALUE function to return the 2nd lowest salary in the entire employees table. In this example, we won’t require a query_partition_clause.

Based on the data in the employees table, enter the following SELECT statement:

```
SELECT DISTINCT NTH_VALUE(salary,2)
OVER (ORDER BY salary ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "SECOND LOWEST"
FROM employees;
```

These are the results that you should see:

```
SECOND LOWEST
-------------
1900
```

In this example, we have used the NTH_VALUE function to find the second lowest salary in the employees table and as such, sorted the salary in ascending order to properly order the data in the analytic window (as specified by ORDER BY salary ASC).

And because we want the second lowest salary for the table (not based on a dept_id), we did not need to include query_partition_clause to partition the data.

## Leave a Review