NTH_VALUE returns the value of measure_expr in the nth row of the window that is defined by analytic_clause. The return value is of the data type of measure_expr.
Syntax
NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)
Parameters
| Parameter | Description |
|---|---|
| OVER | Uses the OVER clause to define a window for calculation. |
| measure_expr | The field name. |
| n | n is a positive number and determines the nth row for which the measurement value is to be returned. If n is NULL, the function returns an error. If n is greater than the number of all the rows in the window, the function returns NULL. |
| FROM { FIRST | LAST } | Specifies whether the calculation starts from the first or last row of the window. The default value is FROM FIRST. |
| {RESPECT | IGNORE} NULLS | Specifies whether to ignore NULL values. The default value is RESPECT NULLS and indicates that NULL values are taken into consideration. |
Return type
The data type of the return value is not limited.
Examples
To create the emp_msg table and insert data into the table, execute the following statements:
CREATE TABLE emp_msg(deptno INT, ename VARCHAR(30),sal INT, MGR VARCHAR(30), hiredate VARCHAR(50));
INSERT INTO emp_msg VALUES(10,'CLARK', 2750, 7839, '2018-05-01');
INSERT INTO emp_msg VALUES(10,'KING', 5300, NULL, '2018-05-10');
INSERT INTO emp_msg VALUES(10,'MILLER', 1600, 7782, '2018-06-01');
INSERT INTO emp_msg VALUES(20,'ADAMS', 1400, 7788, '2018-05-21');
INSERT INTO emp_msg VALUES(20,'FORD', 3300, 7566, '2018-06-01');
INSERT INTO emp_msg VALUES(20,'JONES', 3275, 7839, '2018-06-20');
INSERT INTO emp_msg VALUES(20,'SCOTT', 3300, 7566, '2018-07-01');
INSERT INTO emp_msg VALUES(20,'SMITH', 1100, 7902, '2018-07-10');
INSERT INTO emp_msg VALUES(30,'ALLEN', 1900, 7698, '2018-08-05');
INSERT INTO emp_msg VALUES(30,'BLAKE', 3150, 7839, '2018-06-10');
INSERT INTO emp_msg VALUES(30,'JAMES', 1250, 7698, '2018-09-05');
INSERT INTO emp_msg VALUES(30,'MARTIN', 1550, 7698, '2018-10-01');
INSERT INTO emp_msg VALUES(30,'TURNER', 1800, 7698, '2019-05-01');
INSERT INTO emp_msg VALUES(30,'WARD', 1550, 7698, '2019-05-10');
Group data by department deptno and query the result of the comparison between the salaries of personnel in each department and the salary amount that ranks third in this department. Execute the following statement:
SELECT deptno, ename, sal, nth_value(sal, 3) 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;
The following query result is returned:
+--------+--------+------+----------------+
| DEPTNO | ENAME | SAL | THIRD_MOST_SAL |
+--------+--------+------+----------------+
| 10 | KING | 5300 | 1600 |
| 10 | CLARK | 2750 | 1600 |
| 10 | MILLER | 1600 | 1600 |
| 20 | FORD | 3300 | 3275 |
| 20 | SCOTT | 3300 | 3275 |
| 20 | JONES | 3275 | 3275 |
| 20 | ADAMS | 1400 | 3275 |
| 20 | SMITH | 1100 | 3275 |
| 30 | BLAKE | 3150 | 1800 |
| 30 | ALLEN | 1900 | 1800 |
| 30 | TURNER | 1800 | 1800 |
| 30 | MARTIN | 1550 | 1800 |
| 30 | WARD | 1550 | 1800 |
| 30 | JAMES | 1250 | 1800 |
+--------+--------+------+----------------+