PostgreSQL nth_value() Function

The PostgreSQL nth_value() function returns the evaluated value from the specified row of the window frame associated with the current row.

`nth_value()` Syntax

Here is the syntax of the PostgreSQL nth_value() function:

nth_value(expr, n)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

nth_value(expr, 1) is equivalent to first_value(expr).

Parameters

expr: Required. It can be a column name or an expression.
n: Required. Specifies the number of the row.
partition_column_list: List of columns for partitioning.
partition_column_list: List of columns for sorting.

Return value

The PostgreSQL nth_value() function returns the evaluated value from the specified row of the window frame associated with the current row. This function will return NULL if the specified row does not exist.

`nth_value()` Examples

Preparing Data

Use the following CREATE TABLE statement to create a table named student_grade to store grades of students:

CREATE TABLE student_grade (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class CHAR(1) NOT NULL,
  subject VARCHAR(20) NOT NULL,
  grade INT NOT NULL
);

This student_grade table has 5 columns as following:

id - The row ID, primary key.
name - The name of a student.
class - The class a student is in.
subject - The name of a subject.
grade - The grades for a subject and a student.

Insert some rows into the student_grade table using the following INSERT statement:

INSERT INTO student_grade
  (name, class, subject, grade)
VALUES
  ('Tim', 'A', 'Math', 9),
  ('Tom', 'A', 'Math', 7),
  ('Jim', 'A', 'Math', 8),
  ('Tim', 'A', 'English', 7),
  ('Tom', 'A', 'English', 8),
  ('Jim', 'A', 'English', 7),
  ('Lucy', 'B', 'Math', 8),
  ('Jody', 'B', 'Math', 6),
  ('Susy', 'B', 'Math', 9),
  ('Lucy', 'B', 'English', 6),
  ('Jody', 'B', 'English', 7),
  ('Susy', 'B', 'English', 8);

Use the following SELECT statement to show all rows of this table:

SELECT * FROM student_grade;

 id | name | class | subject | grade
----+------+-------+---------+-------
  1 | Tim  | A     | Math    |     9
  2 | Tom  | A     | Math    |     7
  3 | Jim  | A     | Math    |     8
  4 | Tim  | A     | English |     7
  5 | Tom  | A     | English |     8
  6 | Jim  | A     | English |     7
  7 | Lucy | B     | Math    |     8
  8 | Jody | B     | Math    |     6
  9 | Susy | B     | Math    |     9
 10 | Lucy | B     | English |     6
 11 | Jody | B     | English |     7
 12 | Susy | B     | English |     8
(12 rows)

Example 1

To show the highest-to-lowest grade in each subject and the highest grade in each row, use the following statement:

SELECT
  *,
  nth_value(grade, 1) OVER (
    PARTITION BY subject
    ORDER BY grade DESC
    RANGE BETWEEN
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) first_grade
FROM student_grade;

 id | name | class | subject | grade | first_grade
----+------+-------+---------+-------+-------------
 12 | Susy | B     | English |     8 |           8
  5 | Tom  | A     | English |     8 |           8
 11 | Jody | B     | English |     7 |           8
  4 | Tim  | A     | English |     7 |           8
  6 | Jim  | A     | English |     7 |           8
 10 | Lucy | B     | English |     6 |           8
  1 | Tim  | A     | Math    |     9 |           9
  9 | Susy | B     | Math    |     9 |           9
  7 | Lucy | B     | Math    |     8 |           9
  3 | Jim  | A     | Math    |     8 |           9
  2 | Tom  | A     | Math    |     7 |           9
  8 | Jody | B     | Math    |     6 |           9
(12 rows)

Note that the window function in the SQL statement above:

nth_value(grade, 1) OVER (
  PARTITION BY subject
  ORDER BY grade DESC
) first_grade

In the OVER clause,

PARTITION BY subjectThere will be divisions by subject.
The ORDER BY grade DESC sorts all rows within each partition in descending order by grade.
The RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING defines the window frame relative to the current row in each partition, here means from the first row to the last row in each partition.
The nth_value(grade, 1) returns the grade (grade) in the first row of the window frame that the current row locates. Since the rows is sorted by grade in descending order, so this function returns the highest grade in each partition.

If you want to show the worst grade of each subject in each row, you can replace nth_value(grade, 1) in the above statement with NTH_VALUE(grade, 3).

Example 2

To show the ranking number of each student in each class in descending order of total grade and the best total grade for each class in each row, use the following statement:

SELECT
  t.*,
  nth_value(t.sum_grade, 1) OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
    RANGE BETWEEN
      UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) first_grade
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;

 class | name | sum_grade | first_grade
-------+------+-----------+-------------
 A     | Tim  |        16 |          16
 A     | Jim  |        15 |          16
 A     | Tom  |        15 |          16
 B     | Susy |        17 |          17
 B     | Lucy |        14 |          17
 B     | Jody |        13 |          17
(6 rows)

Notice this subquery in the above statement:

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

This subquery uses the GROUP BY clause and the sum() function sums up each student’s total grade by class and student.

 class | name | sum_grade
-------+------+-----------
 A     | Tim  |        16
 A     | Jim  |        15
 A     | Tom  |        15
 B     | Jody |        13
 B     | Lucy |        14
 B     | Susy |        17
(6 rows)

The main statement partitions all rows from this subquery by classes, then sort by total grade in descending order within each partition, and add the highest grade of each partition to all rows using nth_value(t.sum_grade, 1).

nth_value() Syntax