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.

Introduction to MySQL NTH_VALUE() function

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
)Code language: SQL (Structured Query Language) (sql)

The NTH_VALUE() 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);Code language: SQL (Structured Query Language) (sql)

1) 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;Code language: SQL (Structured Query Language) (sql)

Here is the output:

2) Using MySQL NTH_VALUE() over partitions 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;Code language: SQL (Structured Query Language) (sql)

Here is the output:

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

Summary

• Use the MySQL NTH_VALUE() function to get a value from the Nth row of a result set.