nth_value

Knowledge Checkpoint

Readings that will help you understand this documentation better:

Window Functions Overview

Definition

Returns the value of an expression from the Nth row of the window frame, where N is a positive integer.

Syntax

nth_value(expr, index, order: order_expr, ...)
nth_value(expr, index, order: order_expr, ..., partition: partition_expr, ...)

Examples
nth_value(count(orders.id), 2, order: users.created_at | asc())
nth_value(count(orders.id), 3, order: users.created_at | asc(), order: users.id)

// with partition
nth_value(count(orders.id), 2, order: users.created_at | asc(), partition: orders.status)

Input

expr (required): The expression to retrieve the Nth value from.
index (required, number): The index of the row from which to retrieve the value (1-based).
order (required, repeatable): A field that is used for ordering. The order is default to ascending. The order can be set explicitly with asc() or desc(). E.g. nth_value(count(orders.id), 2, order: users.created_at | desc())
partition (repeatable, optional): A field that is used for partitioning the table. E.g. nth_value(count(orders.id), 2, order: users.created_at, partition: orders.status)

Output

The value of the expression from the Nth row of the window frame. If no partition is specified, the whole table is considered as a single partition.

Notes

This function is not supported in SQL Server.

Definition​

Definition