Purpose
NTH_VALUE() returns the measure_expr value in the nth 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 } | The calculation direction. This parameter is optional. Default value: FROM FIRST.
|
| {RESPECT | IGNORE} NULLS | Optional. Specifies whether to ignore NULLs. 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
The table emp_msg has been created. 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
