{"id":7046,"date":"2018-08-28T07:56:20","date_gmt":"2018-08-28T14:56:20","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7046"},"modified":"2024-01-04T06:43:43","modified_gmt":"2024-01-04T13:43:43","slug":"mysql-first_value-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-first_value-function\/","title":{"rendered":"MySQL FIRST_VALUE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL FIRST_VALUE()<\/code> function to get the first row of a frame, partition, or result set.<\/p>\n\n\n\n

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

The FIRST_VALUE()<\/code> is a window function<\/a> that allows you to select the first row of a window frame, partition, or result set.<\/p>\n\n\n\n

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

FIRST_VALUE (expression) OVER (\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
In this syntax:<\/p>\n\n\n\n
expression<\/h3>\n\n\n\n
The FIRST_VALUE()<\/code> function returns the value of the expression<\/code> from the first row of the window frame.<\/p>\n\n\n\n
The OVER<\/code> clause consists of three clauses: partition_clause<\/code>, order_clause<\/code>, and frame_clause<\/code>.<\/p>\n\n\n\n
partition_clause<\/h3>\n\n\n\n
The partition_clause<\/code> clause divides the rows of the result sets into partitions to which the function applies independently. The partition_clause<\/code> has the following syntax:<\/p>\n\n\n
PARTITION BY expr1, expr2, ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
order_clause<\/h3>\n\n\n\n
The order_clause<\/code> clause specifies the logical order of rows in each partition on which the FIRST_VALUE()<\/code> function operates. The following shows the syntax of the order_clause<\/code>:<\/p>\n\n\n
ORDER BY expr1 [ASC|DESC], expr2 [ASC|DESC], ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL FIRST_VALUE() function examples<\/h2>\n\n\n\n
The following statements create a new table<\/a> named overtime<\/code> and insert<\/a> sample data for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> overtime (\n    employee_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    department VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    hours<\/span> INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (employee_name , department)\n);\nINSERT<\/span> INTO<\/span> overtime(employee_name, department, hours<\/span>)\nVALUES<\/span>('Diane Murphy'<\/span>,'Accounting'<\/span>,37<\/span>),\n('Mary Patterson'<\/span>,'Accounting'<\/span>,74<\/span>),\n('Jeff Firrelli'<\/span>,'Accounting'<\/span>,40<\/span>),\n('William Patterson'<\/span>,'Finance'<\/span>,58<\/span>),\n('Gerard Bondur'<\/span>,'Finance'<\/span>,47<\/span>),\n('Anthony Bow'<\/span>,'Finance'<\/span>,66<\/span>),\n('Leslie Jennings'<\/span>,'IT'<\/span>,90<\/span>),\n('Leslie Thompson'<\/span>,'IT'<\/span>,88<\/span>),\n('Julie Firrelli'<\/span>,'Sales'<\/span>,81<\/span>),\n('Steve Patterson'<\/span>,'Sales'<\/span>,29<\/span>),\n('Foon Yue Tseng'<\/span>,'Sales'<\/span>,65<\/span>),\n('George Vanauf'<\/span>,'Marketing'<\/span>,89<\/span>),\n('Loui Bondur'<\/span>,'Marketing'<\/span>,49<\/span>),\n('Gerard Hernandez'<\/span>,'Marketing'<\/span>,66<\/span>),\n('Pamela Castillo'<\/span>,'SCM'<\/span>,96<\/span>),\n('Larry Bott'<\/span>,'SCM'<\/span>,100<\/span>),\n('Barry Jones'<\/span>,'SCM'<\/span>,65<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
1) Using MySQL FIRST_VALUE() function over the whole query result set example<\/h3>\n\n\n\n
The following statement gets the employee’s name, overtime, and the employee who has the least overtime:<\/p>\n\n\n
SELECT<\/span>\n    employee_name,\n    hours<\/span>,\n    FIRST_VALUE<\/span>(employee_name) OVER<\/span> (\n        ORDER<\/span> BY<\/span> hours<\/span>\n    ) least_over_time\nFROM<\/span>\n    overtime;<\/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 example, the ORDER BY<\/code> clause ordered the rows in the result set by hours and the FIRST_VALUE()<\/code> picked the first row indicating the employee who had the least overtime.<\/p>\n\n\n\n
2) Using MySQL FIRST_VALUE() function with partitions example<\/h3>\n\n\n\n
The following statement finds the employee who has the least overtime in every department:<\/p>\n\n\n
SELECT<\/span>\n    employee_name,\n    department,\n    hours<\/span>,\n    FIRST_VALUE<\/span>(employee_name) OVER<\/span> (\n        PARTITION<\/span> BY<\/span> department\n        ORDER<\/span> BY<\/span> hours<\/span>\n    ) least_over_time\nFROM<\/span>\n    overtime;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  • First, the PARTITION BY<\/code> clause divides the employees into partitions by departments. In other words, each partition consists of employees who belong to the same department.<\/li>\n\n\n\n
  • Second, the ORDER BY<\/code> clause specifies the order of rows in each partition.<\/li>\n\n\n\n
  • Third, the FIRST_VALUE()<\/code> operates on each partition sorted by the hours. It returns the first row in each partition which is the employee who has the least overtime within the department.<\/li>\n<\/ul>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL FIRST_VALUE()<\/code> function to get the first row of a window frame.<\/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
frame_clause<\/h3>\n\n\n\n
The frame_clause<\/code> defines the subset (or frame) of the current partition. For detailed information on the frame clause syntax, check out the window functions tutorial<\/a>.<\/p>\n\n\n\n