Syntax
NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ] [ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)
Purpose
NTH_VALUE() returns the nth value of the measure_expr expression in the searching direction that is defined by [ FROM { FIRST | LAST } ]. The default direction is FROM FIRST. You can specify whether to ignore NULL. The windows are a unified analytic_clause.
n must be a positive integer. If n is NULL, this function returns an error. If n is greater than the total number of rows in the window, this function returns NULL.
Examples
obclient> CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> SELECT last_name, FIRST_VALUE(salary) OVER(PARTITION BY job_id) first_s, LAST_VALUE(salary) OVER(PARTITION BY job_id) last_s, NTH_VALUE(salary,2) OVER(PARTITION BY job_id) 2nd_s FROM employees;
+-----------+---------+--------+-------+
| last_name | first_s | last_s | 2nd_s |
+-----------+---------+--------+-------+
| JIM | 2000 | 2000 | NULL |
| MIKE | 12000 | 11000 | 13000 |
| LILY | 12000 | 11000 | 13000 |
| TOM | 12000 | 11000 | 13000 |
+-----------+---------+--------+-------+
4 rows in set