{"id":1758,"date":"2018-09-09T01:41:20","date_gmt":"2018-09-09T08:41:20","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1758"},"modified":"2025-01-20T03:01:11","modified_gmt":"2025-01-20T10:01:11","slug":"sql-first_value","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-first_value\/","title":{"rendered":"SQL FIRST_VALUE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL FIRST_VALUE()<\/code> function to return the first value in an ordered set of values.<\/p>\n\n\n\n

Overview of SQL FIRST_VALUE() function #<\/a><\/h2>\n\n\n\n

The FIRST_VALUE()<\/code> is a window function<\/a> that returns the first value in an ordered set of values.<\/p>\n\n\n\n

Here’s 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 #<\/a><\/h3>\n\n\n\n
The return value of the expression<\/code> from the first row in a partition or result set.<\/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 #<\/a><\/h3>\n\n\n\n
The partition_clause<\/code> clause 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
The PARTITION BY<\/code> clause divides the rows of the result sets into partitions to which the FIRST_VALUE()<\/code> function applies. If you skip the PARTITION BY<\/code> clause, the function treats the whole result set as a single partition.<\/p>\n\n\n\n
order_clause #<\/a><\/h3>\n\n\n\n
The order_clause<\/code> clause sorts the rows in partitions to which the FIRST_VALUE()<\/code> function applies. The ORDER BY<\/code> clause has the following syntax:<\/p>\n\n\n
ORDER BY expr1 [ASC | DESC], expr2, ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL FIRST_VALUE() function examples #<\/a><\/h2>\n\n\n\n
We will use the employees<\/code> and departments<\/code> tables from the sample database<\/a> to demonstrate the FIRST_VALUE()<\/code> function:<\/p>\n\n\n\n
\"Employees<\/figure>\n\n\n\n
Using SQL FIRST_VALUE() function over result set example #<\/a><\/h3>\n\n\n\n
The following statement finds the employee who has the lowest salary in the company:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  salary,\n  FIRST_VALUE<\/span>(first_name) OVER<\/span> (\n    ORDER<\/span> BY<\/span>\n      salary\n  ) lowest_salary\nFROM<\/span>\n  employees e;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  |  salary  | lowest_salary\n-------------+-------------+----------+---------------\n Karen       | Colmenares  |  2500.00 | Karen\n Guy         | Himuro      |  2600.00 | Karen\n Irene       | Mikkilineni |  2700.00 | Karen\n Sigal       | Tobias      |  2800.00 | Karen\n Shelli      | Baida       |  2900.00 | Karen\n Alexander   | Khoo        |  3100.00 | Karen\n...<\/code><\/span><\/pre>\n\n\n
In this example, the ORDER BY<\/code> clause sorted the employees by salary and the FIRST_VALUE()<\/code> selected the first name of the employee who has the lowest salary.<\/p>\n\n\n\n
Using SQL FIRST_VALUE() over partition example #<\/a><\/h3>\n\n\n\n
The following statement returns the employees who have the lowest salary in each department.<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  department_name,\n  salary,\n  FIRST_VALUE<\/span>(CONCAT<\/span>(first_name, ' '<\/span>, last_name)) OVER<\/span> (\n    PARTITION<\/span> BY<\/span>\n      department_name\n    ORDER<\/span> BY<\/span>\n      salary\n  ) lowest_salary\nFROM<\/span>\n  employees e\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  | department_name  |  salary  |   lowest_salary\n-------------+-------------+------------------+----------+-------------------\n William     | Gietz       | Accounting       |  8300.00 | William Gietz\n Shelley     | Higgins     | Accounting       | 12000.00 | William Gietz\n Jennifer    | Whalen      | Administration   |  4400.00 | Jennifer Whalen\n Lex         | De Haan     | Executive        | 17000.00 | Lex De Haan\n Neena       | Kochhar     | Executive        | 17000.00 | Lex De Haan\n Steven      | King        | Executive        | 24000.00 | Lex De Haan\n Luis        | Popp        | Finance          |  6900.00 | Luis Popp\n Ismael      | Sciarra     | Finance          |  7700.00 | Luis Popp\n Jose Manuel | Urman       | Finance          |  7800.00 | Luis Popp\n John        | Chen        | Finance          |  8200.00 | Luis Popp\n Daniel      | Faviet      | Finance          |  9000.00 | Luis Popp\n Nancy       | Greenberg   | Finance          | 12000.00 | Luis Popp\n...<\/code><\/span><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
frame_clause #<\/a><\/h3>\n\n\n\n
The frame_clause<\/code> defines the subset (or frame) of the current partition. Check out the window function<\/a> tutorial for the detailed information of the frame clause.<\/p>\n\n\n\n