Purpose
You can call this function to return the measure_expr value in the n-th row in the window defined in analytic_clause.
Syntax
NTH_VALUE (measure_expr, n)
[ FROM { FIRST LAST } ][ { RESPECT IGNORE } NULLS ]
OVER (analytic_clause)
Parameters
| Parameter | Description |
|---|---|
| measure_expr | Specifies the field of the data to return. |
| n | Specifies the nth row of the measured values to return. n is a positive integer. Notice
|
| FROM { FIRST LAST } | (Optional) Specifies the calculation direction. Default value: FROM FIRST.
|
| {RESPECT IGNORE} NULLS | (Optional) Specifies whether to ignore NULL values. Default value: RESPECT NULLS.
|
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information about analytic functions, see Analytic functions. |
Return types
This function returns the same value type as the measure_expr parameter.
Examples
Assume that you have created the table emp_msg. Query the emp_msg table. Group the data records in the table by the deptno column and sort the data records by the sal column in descending order. Return the 4th value in the sal column.
obclient> SELECT * FROM emp_msg;
+--------+--------+------+------+
DEPTNO ENAME SAL MGR
+--------+--------+------+------+
10 CLARK 2750 7839
10 KING 5300 NULL
10 MILLER 1600 7782
20 ADAMS 1400 7788
20 FORD 3300 7566
20 JONES 3275 7839
20 SCOTT 3300 7566
20 SMITH 1100 7902
30 ALLEN 1900 7698
30 BLAKE 3150 7839
30 JAMES 1250 7698
30 MARTIN 1550 7698
30 TURNER 1800 7698
30 WARD 1550 7698
30 SCLARK 1750 7839
+--------+--------+------+------+
15 rows in set
obclient> SELECT deptno,ename,sal,
NTH_VALUE(sal,4) OVER (PARTITION BY deptno ORDER BY sal DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_most_sal
FROM emp_msg
ORDER BY deptno,sal DESC;
+--------+--------+------+----------------+
DEPTNO ENAME SAL THIRD_MOST_SAL
+--------+--------+------+----------------+
10 KING 5300 NULL
10 CLARK 2750 NULL
10 MILLER 1600 NULL
20 FORD 3300 1400
20 SCOTT 3300 1400
20 JONES 3275 1400
20 ADAMS 1400 1400
20 SMITH 1100 1400
30 BLAKE 3150 1750
30 ALLEN 1900 1750
30 TURNER 1800 1750
30 SCLARK 1750 1750
30 MARTIN 1550 1750
30 WARD 1550 1750
30 JAMES 1250 1750
+--------+--------+------+----------------+
15 rows in set