{"id":7060,"date":"2018-08-28T08:47:37","date_gmt":"2018-08-28T15:47:37","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7060"},"modified":"2024-01-04T06:47:43","modified_gmt":"2024-01-04T13:47:43","slug":"mysql-nth_value-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-nth_value-function\/","title":{"rendered":"MySQL NTH_VALUE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the NTH_VALUE()<\/code> function to get a value from the Nth row in a result set.<\/p>\n\n\n\n

Introduction to MySQL NTH_VALUE() function<\/h2>\n\n\n\n

The NTH_VALUE()<\/code> is a window function<\/a> that allows you to get a value from the Nth row in an ordered set of rows.<\/p>\n\n\n\n

The following shows the syntax of the NTH_VALUE()<\/code> function:<\/p>\n\n\n

NTH_VALUE(expression, N)\nFROM FIRST\nOVER (\n    partition_clause\n    order_clause\n    frame_clause\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The FROM FIRST<\/code> instructs the NTH_VALUE()<\/code> function to begin calculation at the first row of the window frame.<\/p>\n\n\n\n
Note that SQL standard supports both FROM FIRST<\/code> and FROM LAST<\/code>. However, MySQL only supports FROM FIRST<\/code>. If you want to simulate the effect of FROM LAST<\/code>, then you can use the ORDER BY<\/code> in the over_clause<\/code> to sort the result set in reverse order.<\/p>\n\n\n\n
MySQL NTH_VALUE() function examples<\/h2>\n\n\n\n
We will create a new table<\/a> named basic_pay<\/code> for the demonstration.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> basic_pays(\n    employee_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    department VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    salary INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (employee_name , department)\n);\n\nINSERT<\/span> INTO<\/span> \n\tbasic_pays(employee_name, \n\t\t\t   department, \n\t\t\t   salary)\nVALUES<\/span>\n\t('Diane Murphy'<\/span>,'Accounting'<\/span>,8435<\/span>),\n\t('Mary Patterson'<\/span>,'Accounting'<\/span>,9998<\/span>),\n\t('Jeff Firrelli'<\/span>,'Accounting'<\/span>,8992<\/span>),\n\t('William Patterson'<\/span>,'Accounting'<\/span>,8870<\/span>),\n\t('Gerard Bondur'<\/span>,'Accounting'<\/span>,11472<\/span>),\n\t('Anthony Bow'<\/span>,'Accounting'<\/span>,6627<\/span>),\n\t('Leslie Jennings'<\/span>,'IT'<\/span>,8113<\/span>),\n\t('Leslie Thompson'<\/span>,'IT'<\/span>,5186<\/span>),\n\t('Julie Firrelli'<\/span>,'Sales'<\/span>,9181<\/span>),\n\t('Steve Patterson'<\/span>,'Sales'<\/span>,9441<\/span>),\n\t('Foon Yue Tseng'<\/span>,'Sales'<\/span>,6660<\/span>),\n\t('George Vanauf'<\/span>,'Sales'<\/span>,10563<\/span>),\n\t('Loui Bondur'<\/span>,'SCM'<\/span>,10449<\/span>),\n\t('Gerard Hernandez'<\/span>,'SCM'<\/span>,6949<\/span>),\n\t('Pamela Castillo'<\/span>,'SCM'<\/span>,11303<\/span>),\n\t('Larry Bott'<\/span>,'SCM'<\/span>,11798<\/span>),\n\t('Barry Jones'<\/span>,'SCM'<\/span>,10586<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
1) Using MySQL NTH_VALUE() function over the result set<\/h3>\n\n\n\n
The following statement uses the NTH_VALUE()<\/code> function to find the employee who has the second highest salary :<\/p>\n\n\n
SELECT<\/span>\n    employee_name,\n    salary,\n    NTH_VALUE<\/span>(employee_name, 2<\/span>) OVER<\/span>  (\n        ORDER<\/span> BY<\/span> salary DESC<\/span>\n    ) second_highest_salary\nFROM<\/span>\n    basic_pays;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
2) Using MySQL NTH_VALUE()\u00a0over partitions example<\/h3>\n\n\n\n
The following query finds the employee who has the second highest salary in every department:<\/p>\n\n\n
SELECT<\/span>\n\temployee_name,\n\tdepartment,\n\tsalary,\n\tNTH_VALUE<\/span>(employee_name, 2<\/span>) OVER<\/span>  (\n\t\tPARTITION<\/span> BY<\/span> department\n\t\tORDER<\/span> BY<\/span> salary DESC<\/span>\n\t\tRANGE<\/span> BETWEEN<\/span> UNBOUNDED<\/span> PRECEDING<\/span> AND<\/span> UNBOUNDED<\/span> FOLLOWING<\/span>\n\t) second_highest_salary\nFROM<\/span>\n\tbasic_pays;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this query, we added the PARTITION BY<\/code> clause to divide the employees by department. Then the NTH_VALUE()<\/code> function is applied to each partition independently.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL NTH_VALUE()<\/code> function to get a value from the Nth row of a result set.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
The NTH_VALUE()<\/code> function returns the value of expression<\/code> from the Nth row of the window frame. If that Nth row does not exist, the function returns NULL<\/a><\/code>. N must be a positive integer e.g., 1, 2, and 3.<\/p>\n\n\n\n