{"id":53433,"date":"2019-10-15T11:19:57","date_gmt":"2019-10-15T11:19:57","guid":{"rendered":"https:\/\/www.sqlshack.com\/?p=53433"},"modified":"2019-12-09T10:11:06","modified_gmt":"2019-12-09T10:11:06","slug":"sql-lag-function-overview-and-examples","status":"publish","type":"post","link":"https:\/\/www.sqlshack.com\/sql-lag-function-overview-and-examples\/","title":{"rendered":"SQL Lag function overview and examples"},"content":{"rendered":"

\n In the article SQL Server Lead function overview and examples<\/a>, we explored Lead function for performing computational operations on data. This article gives an overview of the SQL Lag function and its comparison with the SQL Lead function.\n<\/p>\n

<\/p>\n

Overview of SQL Lag function<\/h2>\n

\n We use a Lag() function to access previous rows data as per defined offset value. It is a window function available from SQL Server 2012 onwards. It works similar to a Lead function. In the lead function, we access subsequent rows, but in lag function, we access previous rows. It is a useful function in comparing the current row value from the previous row value.\n<\/p>\n

Syntax of Lag function<\/h3>\n
LAG (scalar_expression [,offset] [,default])  \r\nOVER ( [ partition_by_clause ] order_by_clause )<\/pre>\n
\n  It uses following arguments.\n<\/p>\n
    \n
  • \n    Scalar_expression: We define a column name or expression in this argument. The lag function does calculations on this column. It is a mandatory argument, and we cannot execute the lag function without this\n  <\/li>\n
  • \n    Offset: We define an integer number in this argument. The lag function uses this argument forgo behind the number of rows (offset). The default value for this argument is one. It is an optional argument\n  <\/li>\n
  • \n    Default: Suppose we define an offset, value that does not lie in the boundary of the data. For example, we specified offset value 3 for the first row. A lag function cannot go three rows behind. It displays the default value if specified. If we do not specify any value for this, the lag function displays NULL in the output for out of range values\n  <\/li>\n
  • \n    PARTITION BY: It creates a logical boundary of data. Suppose we have an extensive data set and we require calculations on a smaller set of data, we can define partitions for it. For example, sales data for an organization might contain data for several years. We can create a partition quarterly and do the computation. It is as well an optional argument\n  <\/li>\n
  • \n    ORDER BY: We can sort data in ascending or descending order using ORDER by clause. By default, it uses ascending order to sort data\n  <\/li>\n<\/ul>\n
    \n  We will use data from the previous article for demonstration of SQL Server Lag function as well:\n<\/p>\n
    DECLARE   @Employee TABLE\r\n  (\r\n       EmpCode VARCHAR(10),\r\n       EmpName   VARCHAR(10),\r\n       JoiningDate  DATE\r\n    )\r\nINSERT INTO @Employee VALUES ('1', 'Rajendra', '1-Sep-2018')\r\nINSERT INTO @Employee VALUES ('2', 'Manoj', '1-Oct-2018')\r\nINSERT INTO @Employee VALUES ('3', 'Sonu', '10-Mar-2018')\r\nINSERT INTO @Employee VALUES ('4', 'Kashish', '25-Oct-2018')\r\nINSERT INTO @Employee VALUES ('5', 'Tim', '1-Dec-2018')\r\nINSERT INTO @Employee VALUES ('6', 'Akshita', '1-Nov-2018')\r\nGO\r\nSELECT * FROM   @Employee;<\/pre>\n
    \n  We have the following data in the Employee table:\n<\/p>\n
    \n  \"Sample
    Example 1: SQL Lag function without a default value<\/h3>\n
    \n  Execute the following query to use the Lag function on the JoiningDate column with offset one. We did not specify any default value in this query.\n<\/p>\n
    \n  Execute the following query (we require to run the complete query along with defining a variable, its value):\n<\/p>\n
    SELECT *, \r\n       Lag(JoiningDate, 1) OVER(.\r\n       ORDER BY JoiningDate ASC) AS EndDate\r\nFROM @Employee;<\/pre>\n
    \n  In the output, we can note the following:\n<\/p>\n
      \n
    • \n    The first row shows NULL value for the EndDate column because it does not have any previous rows\n  <\/li>\n
    • \n    The second row contains previous row value in the EndDate column. It takes value from the previous row due to offset value 1\n  <\/li>\n<\/ul>\n
      \n  \"Lag
      Example 2: SQL Lag function with a default value<\/h3>\n
      \n  In the previous example, we get NULL value as a default value. Let’s use a default end date in the lag function. This example also uses offset value 1 in the lag function:\n<\/p>\n
      SELECT *, \r\n       Lag(JoiningDate, 1,'1999-09-01') OVER(\r\n       ORDER BY JoiningDate ASC) AS EndDate\r\nFROM @Employee;<\/pre>\n
      \n  In the output, we can see a default value instead of NULL in the first row:\n<\/p>\n
      \n  \"Lag
      \n  We can use compatible data types in the default value column. If we use incompatible data types, we get the following error message:\n<\/p>\n
      \n  \"Error
      Example 3: SQL Lag function with OFFSET value 2<\/h3>\n
      \n  Previously, we used default offset 1 in Lag function, and it takes value from the previous row. In the example, we use offset value 2. In the output, you can see we have a default value for row 1 and 2. In row 3, it takes value from row 1:\n<\/p>\n
      SELECT *, \r\n       Lag(JoiningDate, 2,'1999-09-01') OVER(\r\n       ORDER BY JoiningDate ASC) AS EndDate\r\nFROM @Employee;<\/pre>\n
      \n  \"Lag
      Example 4: SQL Lag function with PARTITION BY clause<\/h3>\n
      \n  As discussed earlier, we use the PARTITION BY clause to create a logical subset of data. Let’s use this PARTITION function on the ProductSales table. You can refer to the SQL Server Lead function to create this table:\n<\/p>\n
      \n  \"SQL
      \n  In the following query, we use SQL Server Lag function and view the output:\n<\/p>\n
      SELECT [Year], \r\n       [Quarter], \r\n       Sales, \r\n       LAG(Sales, 1, 0) OVER(\r\n       ORDER BY [Year], \r\n                [Quarter] ASC) AS [NextQuarterSales]\r\nFROM dbo.ProductSales;<\/pre>\n
      \n  In the output, the lag function considers all rows as a single data set and applies Lag function:\n<\/p>\n
      \n  \"SQL
      \n  In the ProductSales table, we have data for the years of 2017, 2018 and 2019. We want to use a lag function on a yearly basis. We use the PARTITION BY clause on the Year column and define the logical subset of data on a yearly basis. We use the Order by clause on year and quarter columns to sort data first on a yearly basis and then monthly:\n<\/p>\n
      SELECT [Year], \r\n       [Quarter], \r\n       Sales, \r\n       LAG(Sales, 1, 0) OVER(PARTITION BY [Year]\r\n       ORDER BY [Year], \r\n                [Quarter] ASC) AS [NextQuarterSales]\r\nFROM dbo.ProductSales;<\/pre>\n
      \n  In the following screenshot, we can see three partitions of data for 2017,2018 and 2019 year. The Lag function individually works on each partition and calculates the required data:\n<\/p>\n
      \n  \"Lag
      Conclusion<\/h2>\n
      \n  In this article, we learned the SQL Lag function and its usage to retrieve a value from previous rows. Here is the quick summary of the lag function:\n<\/p>\n
        \n
      • \n    Lag function fetches the value from the previous rows based on the offset defined\n  <\/li>\n
      • \n    Offset one is the default offset value, and in this Lag, the function retrieves a value from the previous row\n  <\/li>\n
      • \n    PARTITION BY clause defines a logical boundary of data based on the specified condition\n  <\/li>\n
      • \n    The lag function uses default value NULL for out-of-range data\n  <\/li>\n
      • \n    We can use the Lag function with common table expression, stored procedures, and functions for computation purposes\n  <\/li>\n<\/ul>\n
        <\/div>\n