PostgreSQL – NTH_VALUE Function

In PostgreSQL, the NTH_VALUE() function is used to get a value from the n^th row in a result set.

Here is the syntax of the NTH_VALUE() function:

Syntax:
NTH_VALUE(expression, offset) 
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ]
)

Let’s analyze the above syntax:

• The expression is the column from which the query is to be made or an expression on which the NTH_VALUE() function operates on.
• The offset is a positive integer that sets the row number according to the first row in the window against the value that the expression evaluates.
• The PARTITION BY clause distributes rows of the result set into partitions to which the NTH_VALUE() function applies.
• The ORDER BY clause is used for sorting the result of the query.
• The frame_clause is used to define the subset (or the frame) of the partition being used.

Example 1:

First, create two tables named grocery and groceries:

CREATE TABLE items(
    group_id serial PRIMARY KEY,
    group_name VARCHAR (100) NOT NULL
);

CREATE TABLE groceries(
    gro_id serial PRIMARY KEY,
    gro_name VARCHAR (100) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES grocery (group_id)
);

Now add some data to it:

INSERT INTO groceries (group_name)
VALUES
    ('Cereal'),
    ('Fruit'),
    ('Vegetable');

INSERT INTO groceries (group_name, group_id, price)
VALUES
    ('Wheat', 1, 30),
    ('Rice', 1, 40),
    ('Barley', 1, 50),
    ('Corn', 1, 90),
    ('Apple', 2, 120),
    ('Banana', 2, 70),
    ('Pear', 2, 70),
    ('Mango', 2, 80),
    ('Brinjal', 3, 70),
    ('Capsicum', 3, 150),
    ('Potato', 3, 20);

The below statement uses the NTH_VALUE() function to return all products together with the most expensive product:

SELECT 
    product_id,
    product_name,
    price,
    NTH_VALUE(product_name, 2) 
    OVER(
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;

Output:

Example 2:

The below statement uses the NTH_VALUE() function to return all products with the second most expensive product for each product group:

SELECT 
    product_id,
    product_name,
    price,
    group_id,
    NTH_VALUE(product_name, 2) 
    OVER(
        PARTITION BY group_id
        ORDER BY price DESC
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    )
FROM 
    products;

Output: