How to Use PostgreSQL NTH_VALUE Function

Written by Data Pilot
September 03, 2019
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this article we will learn how to use PostgreSQL NTH_VALUE function.

What is PostgreSQL NTH_VALUE Function

NTH_VALUE is a PostgreSQL function that is responsible for retrieving the nth position of a row of a particular result set within an ordered partition. This function is like the functions LAST_VALUE and FIRST_VALUE however NTH_VALUE allows us to discover an exact position of the value.

Below is the syntax of the PostgreSQL NTH_VALUE.

1
2
3
4
5
6
NTH_VALUE(expression, n)
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ]
)

NTH_VALUE (measure_column, n) [FROM FIRST | FROM LAST] [RESPECT NULLS | IGNORE NULLS] OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

Let’s discuss the above syntax to better understand its parts.

  1. expression is the specified expression or column that we want to retrieve.

  2. n is a positive numeric value that resolves the position of the the row in relation to the starting row in the window that we like to retrieve.

  3. PARTITION BY will allocate the rows into groups or partitions to where the NTH_VALUE was applied.

  4. ORDER BY simply sort the data for every partition based from a specified order.

  5. frame_clause specifies the current partition’s frame or subset.

Creating A Sample Table

We need to create a sample table that we will be used in the succeeding section for the NTH_VALUE function.

We will be creating two (2) table namely team_groups and team, to do this use the following queries.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE team_groups (
   group_id serial PRIMARY KEY,
   group_name VARCHAR (255) NOT NULL
);
 
CREATE TABLE team (
   team_id serial PRIMARY KEY,
   team_name VARCHAR (255) NOT NULL,
   rating DECIMAL (11, 2),
   group_id INT NOT NULL,
   FOREIGN KEY (group_id) REFERENCES team_groups (group_id)
);

Now that we created our table, we will now insert sample data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO team_groups (group_name)
VALUES
   ('Philippines'),
   ('America'),
   ('China');
 
INSERT INTO team (team_name, group_id,rating)
VALUES
   ('Armada', 1, 8),
   ('TeamSolo', 1, 9),
   ('Mineski', 1, 8),
   ('Hydra', 2, 9),
   ('WinterWolf', 2, 8),
   ('ZeroArc', 2, 8),
   ('Panda', 3, 8),
   ('YinYang', 3, 8),
   ('GreatWall', 3, 9);

Example of PostgreSQL NTH_VALUE() Function

Now that we have an overview understanding of the NTH_VALUE and we have our sample data set, we will now use NTH_VALUE in the succeeding section.

Example on How to Use PostgreSQL NTH_VALUE in a Result Set

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    team_id,
    team_name,
    rating,
    NTH_VALUE(team_name, 2)
    OVER(
        ORDER BY rating DESC
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    )
FROM
    team;

The above syntax uses ORDER BY to sort the rating field in descending order, then we use the frame clause to specify that the frame will start at the result set’s first row and will end at the end of the row.

The result should look something like the following.

1
2
3
4
5
6
7
8
9
10
11
12
 team_id | team_name  | rating | nth_value
---------+------------+--------+-----------
       2 | TeamSolo   |   9.00 | Hydra
       4 | Hydra      |   9.00 | Hydra
       9 | GreatWall  |   9.00 | Hydra
       6 | ZeroArc    |   8.00 | Hydra
       7 | Panda      |   8.00 | Hydra
       8 | YinYang    |   8.00 | Hydra
       5 | WinterWolf |   8.00 | Hydra
       3 | Mineski    |   8.00 | Hydra
       1 | Armada     |   8.00 | Hydra
(9 rows)

Example on How to Use PostgreSQL NTH_VALUE in Partition

The below statement will returning all the teams together with the next most highest rated team for each team group.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
    team_id,
    team_name,
    rating,
    group_id,
    NTH_VALUE(team_name, 2)
    OVER(
        PARTITION BY group_id
        ORDER BY rating DESC
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    )
FROM
    team;

Let’s have a look on the above statement in more details.

  1. We use PARTITION BY to be able to distribute the teams in their respective team groups as defined by the data in the column of group_id.

  2. ORDER BY will sort out the team in their respective team group in descending order.

  3. The entire partition was defined as a frame using the frame clause.

  4. Finally we use NTH_VALUE function to retrieve the team_name of the 2nd row of every team group.

The result should resemble something like the following.

1
2
3
4
5
6
7
8
9
10
11
12
 team_id | team_name  | rating | group_id | nth_value
---------+------------+--------+----------+------------
       2 | TeamSolo   |   9.00 |        1 | Armada
       1 | Armada     |   8.00 |        1 | Armada
       3 | Mineski    |   8.00 |        1 | Armada
       4 | Hydra      |   9.00 |        2 | WinterWolf
       5 | WinterWolf |   8.00 |        2 | WinterWolf
       6 | ZeroArc    |   8.00 |        2 | WinterWolf
       9 | GreatWall  |   9.00 |        3 | Panda
       7 | Panda      |   8.00 |        3 | Panda
       8 | YinYang    |   8.00 |        3 | Panda
(9 rows)

Conclusion

In this article we learn how to use the PostgreSQL NTH_VALUE function. We hope you can apply what we’ve demonstrated here to your specific issue or application at hand.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.