How to use NTH_VALUE function in MySQL

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

NTH_VALUE Syntax

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

Data Set

Here we are trying to use HR data which has ID, Name and Salary and will get sort the data based on salary.

SELECT employee_id,first_name,last_name,salary FROM employees ORDER BY salary DESC;

Find the second highest salary

SELECT employee_id,first_name,salary, NTH_VALUE(salary,2) OVER(ORDER BY salary DESC ) FROM employees;

Here is the twist. If you try to get the 3rd highest salary still you get 17000 as a result. Because the 3rd value here is 17000 it will not remove the duplicate values, So we need to use group by to get the 3rd highest value which is 14000.

Find the third highest salary using group by function

SELECT employee_id, first_name, salary, NTH_VALUE(salary,3) OVER(ORDER BY salary DESC ) FROM employees GROUP BY salary;