MySQL Nth_Value Function – A Complete Reference

In this tutorial, we will study the Nth_Value Function which is one of the window functions.

Introduction

Nth_Value Function is a window function that is used to retrieve the value of the Nth row from a window frame. If there is no Nth row then the function returns NULL.

Syntax of MySQL Nth_Value Function

The syntax for using the Nth_Value function is as follows-

NTH_VALUE(expression, N)
From First
Over (
    partition_clause
    order_clause
    frame_clause
)Code language: SQL (Structured Query Language) (sql)

Here, From First is used to instruct the Nth_Value() function to start calculating from the first row of the window frame. Over() clause is used with partition_clause to break data into partitions. The frame_clause specifies how to define a subset.

Examples of MySQL Nth_Value Function

First, we will create a table named info.

Create Table info (
    name Varchar(50) Not Null,
    department Varchar(50) Not Null,
    Salary Int Not Null,
    Primary Key(name, department)
    );
Insert into info(name, department, Salary)
    values
    ('John', 'Accounts', 5550),
    ('David', 'Science', 8890),
    ('Peter', 'Technical', 7788),
    ('William', 'Space', 9987),
    ('Skye', 'Biology', 5333),
    ('Huee', 'Fishery', 9335),
    ('Chuck', 'Computers', 9992),
    ('Lily', 'Management', 8765);Code language: SQL (Structured Query Language) (sql)

Now we will find which name has the second-highest salary in the result. The code for this is-

Select
    name,
    Salary,
    NTH_VALUE(name, 2) OVER  (
        ORDER BY Salary DESC
    ) second_highest_salary
FROM
    info;Code language: SQL (Structured Query Language) (sql)

Output-

Here we can see William has the second highest salary. The Nth_Value function has taken two parameters name and the number which we need. After that, we sorted the table using the ORDER BY Clause in descending order to find to sort it by the highest salary first. Then from the results, we pulled the second highest salary.

Now, we will again insert some more rows for the same department.

Insert into info(name, department, Salary)
    values
    ('Lisa', 'Accounts', 4537),
    ('Kia', 'Science', 8660),
    ('Pete', 'Technical', 77886),
    ('Will', 'Space', 4587),
    ('Betty', 'Biology', 5113),
    ('Rue', 'Fishery', 1234),
    ('Sia', 'Computers', 3324),
    ('Blair', 'Management', 2881);Code language: SQL (Structured Query Language) (sql)

Next, we will find the second highest salary in each department. The code for this is-

Select
	name,
	department,
	Salary,
	NTH_VALUE(name, 2) OVER  (
		PARTITION BY department
		ORDER BY Salary DESC
		RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) second_highest_salary
From
	info;Code language: SQL (Structured Query Language) (sql)

Output-

Here, the PARTITION BY clause is used to divide the names by department. Unbounded Preceding represents the current row and Unbounded Following represents the last row of the partition.

If we want to find the highest salary till now then the code for it is-

Select
    name,
    Salary,
    Nth_VALUE (name,1) OVER (
    ORDER BY Salary DESC
    ) Highest_salary
    From
    info;Code language: SQL (Structured Query Language) (sql)

Output-

Conclusion

In this tutorial, we studied Nth_Value Function and how to find the Nth row in the result set. For more references, visit the official documentation of MySQL.