How to get the nth value of partition in PostgreSQL

Overview

The NTH_VALUE() function in PostgreSQL is used to return a value from the nth row in an ordered partition of a result set.

Syntax

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

Parameters

  • expression: The target column or expression to retrieve.
  • offset: This is the row number in relation to the first row in the window against which the expression is evaluated. This is determined by the offset, which is a positive integer (higher than zero).
  • PARTITION BY: This will divide the rows into groups or partitions to which the NTH_VALUE will be applied.
  • ORDER BY: This sorts the data in every partition based on a specified order.
  • frame_clause: This specifies the current partition’s frame or subset.

Let’s understand this function with the help of an example.

Consider the following student dataset. The dataset is a student dataset that contains student name, their GPA, and the country they belong to.

student_id,student_name,gpa,country
1,Maya Wells,4.5,USA
2,Olympia Woods,5.9,Australia
3,Kenneth Oneal,8.5,Germany
4,Tobias Garcia,3,Ukraine
5,Micah Mcgee,9,Austria
6,John Mack,5,USA
7,Jack Daniels,6.7,Australia
8,Sarah Daniels,1.3,Australia
9,John Wick,10,USA
10,Zelensky,1,Ukraine
11,Jack Som,8.6,Austria

We can use the NTH_VALUE() to get the 2nd rank student in every country.

The query is as follows:

SELECT
    student_id,
    student_name,
    gpa,
    country,
    NTH_VALUE(student_name, 2)
    OVER (
        PARTITION BY country
        ORDER BY gpa DESC
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    )
FROM
    student;

Let’s understand the above query.

  • PARTITION BY country: This is used to divide the dataset by country, such as students belonging to the same country are in one partition.
  • ORDER BY gpa DESC: This is used as we need to find the 2nd rank student ordered by the GPA.
  • NTH_VALUE(student_name, 2): This is used to get the 2nd row in every partition. In other words, 2nd rank student name in every country.
  • The frame clause defined the whole partition as a frame.

Code

CREATE TABLE student(
   student_id   INTEGER  NOT NULL PRIMARY KEY, 
   student_name VARCHAR(13) NOT NULL,
   gpa          NUMERIC(3,1) NOT NULL,
   country      VARCHAR(9) NOT NULL
);
INSERT INTO student(student_id,student_name,gpa,country) VALUES (1,'Maya Wells',4.5,'USA');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (2,'Olympia Woods',5.9,'Australia');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (3,'Kenneth Oneal',8.5,'Germany');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (4,'Tobias Garcia',3,'Ukraine');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (5,'Micah Mcgee',9,'Austria');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (6,'John Mack',5,'USA');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (7,'Jack Daniels',6.7,'Australia');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (8,'Sarah Daniels',1.3,'Australia');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (9,'John Wick',10,'USA');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (10,'Zelensky',1,'Ukraine');
INSERT INTO student(student_id,student_name,gpa,country) VALUES (11,'Jack Som',8.6,'Austria');
SELECT
    student_id,
    student_name,
    gpa,
    country,
    NTH_VALUE(student_name, 2)
    OVER (
        PARTITION BY country
        ORDER BY gpa DESC
        RANGE BETWEEN
            UNBOUNDED PRECEDING AND
            UNBOUNDED FOLLOWING
    )
FROM
    student;

Explanation

  • Lines 1–6: We create the student table.
  • Lines 8–18: We insert the sample records into the student table.
  • Lines 20–3: We use the NTH_VALUE() to get the 2nd rank student in every country.
Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
YouTubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)