Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

postgresql

How to get the nth value of partition in PostgreSQL

abhilash

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.

RELATED TAGS

postgresql
RELATED COURSES

View all Courses

Keep Exploring