All Products
Search
Document Center

NTH_VALUE

Last Updated: Jun 18, 2021

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 |
+--------+--------+------+----------------+