PostgreSQL – NTH_VALUE Function

The PostgreSQL NTH_VALUE() function is an essential tool in advanced SQL queries for analytical purposes. It allows us to retrieve the value from the nth row in an ordered set within a specified window. This functionality is invaluable when we need to pinpoint specific data points, such as the nth highest value or nth row in a group, making it a powerful function in the PostgreSQL window functions.

In this article, we will cover the syntax, usage, and practical examples of the PostgreSQL NTH_VALUE() function, highlighting how it can be applied for various data analysis tasks.

What is the PostgreSQL NTH_VALUE Function?

The NTH_VALUE() function is used in PostgreSQL to return the value of an expression from the nth row within a specified window of rows. This is particularly useful when we need to retrieve a specific row value in relation to other rows in the partitioned or ordered dataset.

Syntax

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

Key Terms

• expression: The column or expression from which the value is to be retrieved.
• offset: A positive integer that indicates the row number, starting from the first row in the window.
• PARTITION BY clause: Distributes rows into partitions to which the NTH_VALUE() function is applied.
• ORDER BY clause: Sorts the result set to determine the nth row.
• frame_clause: Defines the subset (or frame) of the partition used by the function.

Examples of PostgreSQL NTH_VALUE Function

Let us take a look at some of the examples of the NTH_VALUE Function in PostgreSQL to better understand the concept. where we will create two tables named ‘items’ and ‘groceries’:, insert values, and then query the data using the NTH_VALUE() function.

Step 1: Create Tables

We will create two tables, items and groceries, to store data about product groups and grocery items.

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)
);

Step 2: Insert Values into Tables

Now, let’s insert some sample data into both tables: items (for product groups) and groceries (for grocery items with their prices).

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);

Example 1: Using NTH_VALUE() to Find the Most Expensive Products

Suppose we have a products table and we want to retrieve the most expensive product. The below statement uses the NTH_VALUE() function to get the most expensive product in the list.

Query:

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

Explanation:

• This query retrieves all products and also shows the most expensive product based on the price column.
• The NTH_VALUE(product_name, 2) function finds the product at the second position when ordered by price DESC.
• The window frame RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures that all rows are considered for the nth value.

Example 2: Finding the Second Most Expensive Product per Group

The below PostgreSQL query uses the NTH_VALUE() function to return all products with the second most expensive product for each product group, making the process easy.

Query:

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

Explanation:

• The query uses the PARTITION BY group_id clause to group the products by their respective categories.
• It then orders each group by price DESC to determine the second most expensive product in each group.
• The NTH_VALUE() function retrieves the second highest product in each group

Important Points About PostgreSQL NTH_VALUE() Function

• The NTH_VALUE() function retrieves the value of an expression from the nth row within a specified window of rows, providing powerful analytical capabilities for complex queries.
• If the nth row does not exist within the window frame, the function returns NULL.
• By using the PARTITION BY clause, we can perform the nth value calculation within each partition separately.
• The window frame clause is optional but can be used to define a subset of the partition. Common clauses include: ‘ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING’ and ‘ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING’.

Conclusion

The PostgreSQL NTH_VALUE() function is an essential analytical tool for querying data in complex datasets. By using this function, we can easily extract specific values like the nth row from an ordered result set. Whether we are dealing with pricing data, product rankings, or time-series analysis, NTH_VALUE() offers great flexibility and precision. With its ability to partition data and order rows within each partition, the NTH_VALUE() function is incredibly powerful for both business intelligence and data analysis tasks

FAQs

What is nth_value in SQL?

The nth_value() function in SQL is a window function that returns the value of the specified column from the nth row within the window frame. It allows you to retrieve a specific row’s value based on the defined order and partitioning.

What is the nth value window function in PostgreSQL?

In PostgreSQL, the nth_value() window function is used to return the value from a specific row (the nth row) in the result set, within a defined window frame. It’s useful for retrieving specific values from a sorted set of data.

How to select nth row in PostgreSQL?

To select the nth row in PostgreSQL, you can use the LIMIT and OFFSET clauses. For example, to select the 5th row, you can use the query:
SELECT * FROM table_name LIMIT 1 OFFSET 4;. This retrieves one row starting from the 5th row (0-based index)

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• PostgreSQL-function
• PostgreSQL-Window-function