GBase 8a OLAP: Using NTH_VALUE to Fetch the N‑th Row in a Window

NTH_VALUE is a window function in GBase 8a that returns the value of a specified column from the n‑th row within the current window. Unlike FIRST_VALUE, which always gives the first row, NTH_VALUE lets you target any row position. This article explains the syntax and demonstrates typical usage with clear examples.

Syntax

NTH_VALUE(field, num) OVER (
    [PARTITION BY col1, col2, ...]
    ORDER BY col1 [ASC|DESC], col2 [ASC|DESC], ...
)

• field: The column whose value you want to retrieve.
• num: The row number within the window (1‑based). Must be greater than 0. If the current window has fewer rows than num, the function returns NULL.

Sample Data

CREATE TABLE td(id INT, type INT, val DECIMAL(20,3));
INSERT INTO td VALUES
    (1,1,111), (1,1,222), (2,1,333), (2,2,444),
    (3,2,555), (3,2,666), (4,2,777),
    (5,1,NULL), (50,2,NULL);

Basic Behaviour

Partition by type and order by id ascending. Then fetch the 1st, 2nd, and 3rd values in each partition:

-- 1st row: equivalent to FIRST_VALUE
SELECT id, type, val,
       NTH_VALUE(val, 1) OVER (PARTITION BY type ORDER BY id) AS first_val
FROM td;

-- 2nd row: first row returns NULL, subsequent rows return the partition's 2nd row value
SELECT id, type, val,
       NTH_VALUE(val, 2) OVER (PARTITION BY type ORDER BY id) AS second_val
FROM td;

-- 3rd row: first two rows return NULL, following rows get the 3rd row value
SELECT id, type, val,
       NTH_VALUE(val, 3) OVER (PARTITION BY type ORDER BY id) AS third_val
FROM td;

The outputs are shown below:

id | type | val      | first_val
---|------|----------|----------
 1 |    1 | 111.000  | 111.000
 1 |    1 | 222.000  | 111.000
 2 |    1 | 333.000  | 111.000
 5 |    1 |    NULL  | 111.000
 2 |    2 | 444.000  | 444.000
 3 |    2 | 555.000  | 444.000
 3 |    2 | 666.000  | 444.000
 4 |    2 | 777.000  | 444.000
50 |    2 |    NULL  | 444.000

id | type | val      | second_val
---|------|----------|-----------
 1 |    1 | 111.000  | NULL
 1 |    1 | 222.000  | 222.000
 2 |    1 | 333.000  | 222.000
 5 |    1 |    NULL  | 222.000
 2 |    2 | 444.000  | NULL
 3 |    2 | 555.000  | 555.000
 3 |    2 | 666.000  | 555.000
 4 |    2 | 777.000  | 555.000
50 |    2 |    NULL  | 555.000

id | type | val      | third_val
---|------|----------|----------
 1 |    1 | 111.000  | NULL
 1 |    1 | 222.000  | NULL
 2 |    1 | 333.000  | 333.000
 5 |    1 |    NULL  | 333.000
 2 |    2 | 444.000  | NULL
 3 |    2 | 555.000  | NULL
 3 |    2 | 666.000  | 666.000
 4 |    2 | 777.000  | 666.000
50 |    2 |    NULL  | 666.000

• Omitting ORDER BY makes the window order undefined, producing unpredictable results.
• Omitting PARTITION BY treats the entire result set as a single window.

-- No ORDER BY: results have no practical meaning
SELECT id, type, val,
       NTH_VALUE(val, 3) OVER (PARTITION BY type) AS third_val
FROM td;

-- No PARTITION BY: all rows share the same window
SELECT id, type, val,
       NTH_VALUE(val, 3) OVER (ORDER BY id) AS third_val
FROM td;

NTH_VALUE provides a clean, windowed way to retrieve positional data in OLAP queries. In a gbase database, you can use it to answer questions like “the third order amount per customer” or “the second sensor reading in each time window”, making your analytical SQL both concise and readable.