NTH_VALUE function in Oracle

NTH_VALUE is one of the vital Analytic functions of Oracle. It is used to get a specific value in an ordered set of values from an analytic window. The NTH_VALUE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c and Oracle 11g Release 2.

Syntax:

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

Parameters: measure_column: It is used to specify the expressions or columns to be returned. n: It is used to specify the nth value of measure_column in the analytic window which needs to be retrieved. FROM FIRST | FROM LAST: It is an optional parameter which is used to specify whether to start the calculation at the first row or the last row of the analytic window. The default value is FROM FIRST. RESPECT NULLS | IGNORE NULLS: It is an optional parameter which is used to specify whether to include or ignore the NULL values in the calculation. The default value is RESPECT NULLS. query_partition_clause: It is also an optional parameter which is used to partition the results into groups. order_by_clause: It is also an optional parameter which is used to order the data within each partition. windowing_clause: It is also an optional parameter which is used to specify the rows in the analytic window to be evaluated.

Values of windowing_clause: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: It is the default value that changes the Last row in the window with a change in the current row. RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: It changes the First row in the windows with a change in the current row. RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: It includes all the rows in the window, regardless of the current row.

Example 1: Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT DISTINCT NTH_VALUE (marks, 1)
 OVER (ORDER BY marks DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "HIGHEST"
FROM students;

Output:

HIGHEST
100

Explanation: Here, the NTH_VALUE function is used to get the highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in descending order. The windowing clause will then include all the rows in the window, regardless of the current row.

Example 2: Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15

Query:

SELECT DISTINCT NTH_VALUE (marks, 2)
 OVER (ORDER BY marks DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "SECOND HIGHEST"
FROM students;

Output:

SECOND HIGHEST 95

Explanation: Here, the NTH_VALUE function is used to get the second highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in descending order. The windowing clause will then include all the rows in the window, regardless of the current row.

Example 3: Students Table:

ID	MARKS	EXTRA_MARKS
1	80	10
2	100	20
3	95	15
4	75	10
5	85	15
6	90	15
7	98	20
8	79	10

Query:

SELECT DISTINCT extra_marks, NTH_VALUE (marks, 1)
 OVER (PARTITION BY extra_marks ORDER BY marks DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "HIGHEST"
NTH_VALUE (marks, 2)
 OVER (PARTITION BY extra_marks ORDER BY marks DESC
       RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       AS "SECOND HIGHEST"
FROM students
WHERE extra_marks in (10, 15)
ORDER BY extra_marks;

Output:


EXTRA_MARKS	HIGHEST	SECOND HIGHEST
10	        80	79
15	        95	90

Explanation: Here, the NTH_VALUE function is used to get the highest ‘marks’ and the second highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in descending order after the partition of the results by ‘extra_marks’. The windowing clause will then include all the rows in the window, regardless of the current row.

Please follow and like us:
Content Protection by DMCA.com