Syntax
NTH_VALUE (measure_expr, n) [ FROM { FIRST LAST } ] [ { RESPECT IGNORE } NULLS ] OVER (analytic_clause)
Purpose
You can call this function to return the nth value in the searching direction that is defined by [ FROM { FIRST LAST } ]. The default direction is FROM FIRST. You can specify whether to ignore NULLs. 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 EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.08 sec)
obclient> INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.11 sec)
obclient> INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.00 sec)
obclient> INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.01 sec)
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 EXPLOYEES;
+-----------+---------+--------+-------+
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 (0.00 sec)