In this tutorial, we will study the Nth_Value Function which is one of the window functions.
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 )
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);
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;
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);
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;
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;
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.