MySQL NTH_VALUE

The MySQL NTH_VALUE function is a window function that returns the value of a given expression from the Nth row of the window frame. It is useful for retrieving values from a specific position within an ordered set of rows.

Syntax

The syntax of the NTH_VALUE function is as follows:

NTH_VALUE(expr, N) OVER ([PARTITION BY ...] [ORDER BY ...])

Here’s a breakdown of the syntax:

expr: The expression whose value you want to retrieve.
N: The Nth row from the window frame. N must be a non-negative integer.
PARTITION BY: (Optional) Divides the window frame into smaller partitions based on the specified columns. This allows you to apply the NTH_VALUE() function independently to each partition.
ORDER BY: (Optional) Orders the rows within each partition. The NTH_VALUE() function considers the order specified in the ORDER BY clause when selecting the Nth row.

Examples

Retrieve the second-highest salary:

SELECT 
NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) AS second_highest_salary 
FROM employees;

Find the third-highest salary for each department:

SELECT 
department_id, 
NTH_VALUE(salary, 3) OVER (PARTITION BY department_id ORDER BY salary DESC) AS third_highest_salary
FROM employees;

Get the previous customer ID in the order:

SELECT 
customer_id, 
NTH_VALUE(customer_id, -1) OVER (ORDER BY order_date) AS previous_customer_id
FROM orders;

The NTH_VALUE function is a powerful tool for analyzing and manipulating ordered data in MySQL. It can be used to extract specific values from a window frame, enabling you to perform complex calculations and transformations on your data.