MySQL NTH_VALUE Function
The NTH_VALUE()
function in MySQL is a window function introduced in MySQL 8.0. It allows you to retrieve the nth value from a result set within a specific window frame.
This function is especially useful when analyzing datasets where you need to fetch specific positional values, such as the second-highest value or third-lowest value within a group.
Syntax
expression
: The column or expression whose nth value is to be retrieved.n
: The position (1-based index) of the value to fetch.PARTITION BY
: Divides the result set into partitions, and the nth value is calculated within each partition.ORDER BY
: Determines the order of rows within the partition or entire dataset.frame_clause
: Specifies the rows considered in the window frame.
Frame Clauses
The frame_clause
defines the subset of rows to include in the window. Common frame clauses include:
- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:
- Considers all rows from the start of the partition up to the current row.
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:
- Considers all rows in the partition.
Key Points
- If the specified
n
exceeds the number of rows in the partition, the function returnsNULL
. - The function is 1-based;
n = 1
fetches the first value. - The
ORDER BY
clause determines the order in which the nth value is selected.
Example 1: Fetch the 2nd Value in a Column
Suppose you have a table sales
:
sale_id | region | amount |
---|---|---|
1 | North | 100 |
2 | North | 200 |
3 | North | 150 |
4 | South | 300 |
5 | South | 250 |
To retrieve the 2nd value of the amount
column within each partition (region):
Output:
sale_id | region | amount | second_value |
---|---|---|---|
1 | North | 100 | 150 |
3 | North | 150 | 150 |
2 | North | 200 | 150 |
5 | South | 250 | 300 |
4 | South | 300 | 300 |
Example 2: Fetch the 3rd Value Across All Rows
To fetch the 3rd value of amount
across the entire dataset:
Output:
sale_id | region | amount | third_value |
---|---|---|---|
1 | North | 100 | 150 |
3 | North | 150 | 150 |
2 | North | 200 | 150 |
5 | South | 250 | 150 |
4 | South | 300 | 150 |
Example 3: Fetch Last nth Value
To fetch the last nth value (e.g., the second-to-last value), you can use the reverse order:
Output:
sale_id | region | amount | second_last_value |
---|---|---|---|
2 | North | 200 | 150 |
3 | North | 150 | 150 |
1 | North | 100 | 150 |
4 | South | 300 | 250 |
5 | South | 250 | 250 |
Use Cases
Rank-Based Analysis:
- Fetch nth highest or nth lowest value in a dataset.
Sales Insights:
- Identify the second-best or third-best sales in each region.
Data Validation:
- Validate specific values based on their positions in ordered datasets.
Performance Benchmarking:
- Retrieve specific performance metrics, such as the second or third-highest scores.
Conclusion
The NTH_VALUE()
function is a versatile tool in MySQL for fetching positional values within ordered partitions or datasets. It simplifies the retrieval of specific values without needing complex subqueries or manual ranking logic. Understanding its usage with PARTITION BY
and ORDER BY
clauses make it an essential function for advanced analytical queries.