Purpose
NTH_VALUE() returns 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 | The field of the data to return. |
| n | The nth row of the measured values to return. n is a positive integer. Notice
|
| FROM { FIRST | LAST } | Optional. 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, see Analytic functions. |
Return type
The return type is the same as the data type of 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