{"id":1311,"date":"2019-02-23T11:24:38","date_gmt":"2019-02-23T04:24:38","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1311"},"modified":"2020-07-11T04:00:31","modified_gmt":"2020-07-10T21:00:31","slug":"sql-server-first_value-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-window-functions\/sql-server-first_value-function\/","title":{"rendered":"SQL Server FIRST_VALUE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server FIRST_VALUE()<\/code> function to get the first value in an ordered partition of a result set.<\/p>\n\n\n\n

SQL Server FIRST_VALUE() function overview #<\/a><\/h2>\n\n\n\n

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

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

FIRST_VALUE ( scalar_expression )  \nOVER ( \n    [PARTITION BY partition_expression, ... ]\n    ORDER BY sort_expression [ASC | DESC], ...\n)\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
PARTITION BY clause #<\/a><\/h3>\n\n\n\n
The PARTITION BY<\/code> clause distributes rows of the result set into partitions to which the FIRST_VALUE()<\/code> function is applied. If you skip the PARTITION BY<\/code> clause, the FIRST_VALUE()<\/code> function will treat the whole result set as a single partition.<\/p>\n\n\n\n
ORDER BY clause #<\/a><\/h3>\n\n\n\n
The ORDER BY<\/code> clause specifies the logical order of the rows in each partition to which the FIRST_VALUE()<\/code>function is applied.<\/p>\n\n\n\n
rows_range_clause #<\/a><\/h3>\n\n\n\n
The rows_range_clause<\/code> further limits the rows within the partition by defining start and end points.<\/p>\n\n\n\n
SQL Server FIRST_VALUE() function examples #<\/a><\/h2>\n\n\n\n
The following statement creates a new view named sales.vw_category_sales_volume<\/code> that returns the number of products sold by product category and year.<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> \n    sales.vw_category_sales_volume \nAS<\/span>\nSELECT<\/span> \n    category_name, \n    YEAR<\/span>(order_date) year<\/span>, \n    SUM<\/span>(quantity) qty\nFROM<\/span> \n    sales.orders o\nINNER<\/span> JOIN<\/span> sales.order_items i \n    ON<\/span> i.order_id = o.order_id\nINNER<\/span> JOIN<\/span> production.products p \n    ON<\/span> p.product_id = i.product_id\nINNER<\/span> JOIN<\/span> production.categories c \n    ON<\/span> c.category_id = p.product_id\nGROUP<\/span> BY<\/span> \n    category_name, \n    YEAR<\/span>(order_date);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the data from the view:<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span> \n    sales.vw_category_sales_volume\nORDER<\/span> BY<\/span> \n    year<\/span>, \n    category_name, \n    qty;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
A) Using FIRST_VALUE() over a result set example #<\/a><\/h3>\n\n\n\n
This example uses FIRST_VALUE()<\/code> function to return category name with the lowest sales volume in 2017:<\/p>\n\n\n
SELECT<\/span> \n    category_name,\n    year<\/span>,\n    qty,\n    FIRST_VALUE<\/span>(category_name) OVER<\/span>(\n        ORDER<\/span> BY<\/span> qty\n    ) lowest_sales_volume\nFROM<\/span> \n    sales.vw_category_sales_volume\nWHERE<\/span>\n    year<\/span> = 2017<\/span>;\n<\/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
\"SQL<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
scalar_expression #<\/a><\/h3>\n\n\n\n
scalar_expression<\/code> is an expression evaluated against the value of the first row of the ordered partition of a result set. The scalar_expression<\/code> can be a column, subquery<\/a>, or expression that evaluates to a single value. It cannot be a window function.<\/p>\n\n\n\n