MySQL NTH_VALUE Function

 

MySQL NTH_VALUE Function


Summary: in this tutorial, you will learn how to use the NTH_VALUE() function to get a value from the Nth row in a result set.

The NTH_VALUE() is a window function that allows you to get a value from the Nth row in an ordered set of rows.

The following shows the syntax of the NTH_VALUE() function:

NTH_VALUE(expression, N) FROM FIRST OVER ( partition_clause order_clause frame_clause )

The NTH_VALUE() the function returns the value of expression from the Nth row of the window frame. If that Nth row does not exist, the function returns NULL. N must be a positive integer e.g., 1, 2, and 3.

The FROM FIRST instructs the NTH_VALUE() function to begin calculation at the first row of the window frame.

Note that SQL standard supports both FROM FIRST and FROM LAST. However, MySQL only supports FROM FIRST. If you want to simulate the effect of FROM LAST, then you can use the ORDER BY in the over_clause to sort the result set in reverse order.

MySQL NTH_VALUE() function examples

We will create a new table named basic_pay for the demonstration.

CREATE TABLE basic_pays( employee_name VARCHAR(50) NOT NULL, department VARCHAR(50) NOT NULL, salary INT NOT NULL, PRIMARY KEY (employee_name , department) ); INSERT INTO basic_pays(employee_name, department, salary) VALUES ('Diane Murphy','Accounting',8435), ('Mary Patterson','Accounting',9998), ('Jeff Firrelli','Accounting',8992), ('William Patterson','Accounting',8870), ('Gerard Bondur','Accounting',11472), ('Anthony Bow','Accounting',6627), ('Leslie Jennings','IT',8113), ('Leslie Thompson','IT',5186), ('Julie Firrelli','Sales',9181), ('Steve Patterson','Sales',9441), ('Foon Yue Tseng','Sales',6660), ('George Vanauf','Sales',10563), ('Loui Bondur','SCM',10449), ('Gerard Hernandez','SCM',6949), ('Pamela Castillo','SCM',11303), ('Larry Bott','SCM',11798), ('Barry Jones','SCM',10586);

Using MySQL NTH_VALUE() function over the result set

The following statement uses the NTH_VALUE() function to find the employee who has the second-highest salary :

SELECT employee_name, salary, NTH_VALUE(employee_name, 2) OVER ( ORDER BY salary DESC ) second_highest_salary FROM basic_pays;

Here is the output:

MySQL NTH_VALUE Function Example

Using MySQL NTH_VALUE() over partition Example

The following query finds the employee who has the second-highest salary in every department:

SELECT employee_name, department, salary, NTH_VALUE(employee_name, 2) OVER ( PARTITION BY department ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) second_highest_salary FROM basic_pays;

Here is the output:

MySQL NTH_VALUE Function OVER partition example

In this query, we added the PARTITION BY clause to divide the employees by department. Then the NTH_VALUE() the function is applied to each partition independently.

In this tutorial, you have learned how to use the MySQL NTH_VALUE() function to get a value from the Nth row of a result set.

Reactions

Post a Comment

0 Comments