{"id":1298,"date":"2019-02-22T09:47:40","date_gmt":"2019-02-22T02:47:40","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1298"},"modified":"2020-04-11T20:12:49","modified_gmt":"2020-04-11T13:12:49","slug":"sql-server-lag-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-window-functions\/sql-server-lag-function\/","title":{"rendered":"SQL Server LAG Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the LAG()<\/code> function to access a row at a specific physical offset which comes before the current row.<\/p>\n\n\n\n

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

SQL Server LAG()<\/code> is a window function<\/a> that provides access to a row at a specified physical offset which comes before the current row.<\/p>\n\n\n\n

In other words, by using the LAG()<\/code> function, from the current row, you can access data of the previous row, or the row before the previous row, and so on.<\/p>\n\n\n\n

The LAG()<\/code> function can be very useful for comparing the value of the current row with the value of the previous row.<\/p>\n\n\n\n

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

LAG(return_value ,offset [,default]) \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
 return_value #<\/a><\/h3>\n\n\n\n
The return value of the previous row based on a specified offset. The return value must evaluate to a single value and cannot be another window function.<\/p>\n\n\n\n
 offset #<\/a><\/h3>\n\n\n\n
The number of rows back from the current row from which to access data.  offset<\/code> can be an expression, subquery<\/a>, or column that evaluates to a positive integer.<\/p>\n\n\n\n
The default value of offset<\/code> is 1 if you don’t specify it explicitly.<\/p>\n\n\n\n
 default #<\/a><\/h3>\n\n\n\n
default<\/code> is the value to be returned if offset<\/code> goes beyond the scope of the partition. It defaults to NULL<\/code> if it is not specified.<\/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 LAG()<\/code> function is applied.<\/p>\n\n\n\n
If you omit the PARTITION BY<\/code> clause, the 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 LAG()<\/code> function is applied.<\/p>\n\n\n\n
SQL Server LAG() function examples #<\/a><\/h2>\n\n\n\n
We will reuse the view sales.vw_netsales_brands<\/code> created in the LEAD()<\/code> function tutorial for the demonstration.<\/p>\n\n\n\n
The following query shows the data from the sales.vw_netsales_brands<\/code> view:<\/p>\n\n\n
SELECT<\/span> \n\t*\nFROM<\/span> \n\tsales.vw_netsales_brands\nORDER<\/span> BY<\/span> \n\tyear<\/span>, \n\tmonth<\/span>, \n\tbrand_name, \n\tnet_sales;\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
A) Using SQL Server LAG() function over a result set example #<\/a><\/h3>\n\n\n\n
This example uses the LAG()<\/code> function to return the net sales of the current month and the previous month in the year 2018:<\/p>\n\n\n
WITH<\/span> cte_netsales_2018 AS<\/span>(\n\tSELECT<\/span> \n\t\tmonth<\/span>, \n\t\tSUM<\/span>(net_sales) net_sales\n\tFROM<\/span> \n\t\tsales.vw_netsales_brands\n\tWHERE<\/span> \n\t\tyear<\/span> = 2018<\/span>\n\tGROUP<\/span> BY<\/span> \n\t\tmonth<\/span>\n)\nSELECT<\/span> \n\tmonth<\/span>,\n\tnet_sales,\n\tLAG(net_sales,1<\/span>) OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> month<\/span>\n\t) previous_month_sales\nFROM<\/span> \n\tcte_netsales_2018;\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