MySQL NTH_VALUE Function

MySQL NTH_VALUE Function

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

NTH_VALUE(expression, n) OVER ( [PARTITION BY column_name] [ORDER BY column_name] [frame_clause] )
  • 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:

  1. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:
    • Considers all rows from the start of the partition up to the current row.
  2. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:
    • Considers all rows in the partition.

Key Points

  1. If the specified n exceeds the number of rows in the partition, the function returns NULL.
  2. The function is 1-based; n = 1 fetches the first value.
  3. 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_idregionamount
1North100
2North200
3North150
4South300
5South250

To retrieve the 2nd value of the amount column within each partition (region):

SELECT sale_id, region, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY region ORDER BY amount) AS second_value FROM sales;

Output:

sale_idregionamountsecond_value
1North100150
3North150150
2North200150
5South250300
4South300300

Example 2: Fetch the 3rd Value Across All Rows

To fetch the 3rd value of amount across the entire dataset:

SELECT sale_id, region, amount, NTH_VALUE(amount, 3) OVER (ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_value FROM sales;

Output:

sale_idregionamountthird_value
1North100150
3North150150
2North200150
5South250150
4South300150

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:

SELECT sale_id, region, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY region ORDER BY amount DESC) AS second_last_value FROM sales;

Output:

sale_idregionamountsecond_last_value
2North200150
3North150150
1North100150
4South300250
5South250250

Use Cases

  1. Rank-Based Analysis:

    • Fetch nth highest or nth lowest value in a dataset.
  2. Sales Insights:

    • Identify the second-best or third-best sales in each region.
  3. Data Validation:

    • Validate specific values based on their positions in ordered datasets.
  4. 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.

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close