PostgreSQL – NTH_VALUE Function

ByAdmin

The NTH_VALUE() function is used to access the value of a column from the nth row within a result set based on a specified order. This function allows you to retrieve values from rows at a specific position relative to the current row. It’s useful for scenarios where you need to access values from rows other than the immediately preceding or succeeding row.

The syntax of the NTH_VALUE() function is as follows:

NTH_VALUE(expression, n) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
  • expression: The column or expression whose value you want to access from the nth row.
  • n: The position of the row from which you want to access the value. The value of n should be an integer greater than or equal to 1.
  • PARTITION BY: An optional clause that divides the result set into partitions or groups. The function operates separately within each partition. If omitted, the function operates on the entire result set.
  • ORDER BY: Specifies the column(s) by which you want to order the result set for accessing the nth row value.

Here’s a simple example of how to use the NTH_VALUE() function in PostgreSQL:

Suppose you have a table named “sales” with columns “sale_date” and “revenue”, and you want to retrieve the revenue from the third highest sale for each day:

SELECT sale_date, revenue, NTH_VALUE(revenue, 3) OVER (PARTITION BY sale_date ORDER BY revenue DESC) AS third_highest_revenue FROM sales;

In this example, the NTH_VALUE() function retrieves the revenue from the third highest sale for each day based on the order of revenues in descending order. The result might look like this:

In this output, the “third_highest_revenue” column contains the revenue from the third highest sale for each day. The function is applied independently within each day’s partition.

The NTH_VALUE() function is particularly useful when you need to access values from specific positions within a result set based on a particular order.

Similar Posts