{"id":1774,"date":"2018-09-10T08:05:15","date_gmt":"2018-09-10T15:05:15","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1774"},"modified":"2025-01-20T02:16:37","modified_gmt":"2025-01-20T09:16:37","slug":"sql-lag","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/","title":{"rendered":"SQL LAG Function"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to access data of a previous row from the current row using the SQL <code>LAG()<\/code> function.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='overview-of-sql-lag-function'>Overview of SQL LAG() function <a href=\"#overview-of-sql-lag-function\" class=\"anchor\" id=\"overview-of-sql-lag-function\" title=\"Anchor for Overview of SQL LAG() function\">#<\/a><\/h2>\n\n\n\n<p>SQL <code>LAG()<\/code> is a <a href=\"https:\/\/www.sqltutorial.org\/sql-window-functions\/\">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<p>In other words, by using the <code>LAG()<\/code> function, from the current row, you can access data of the previous row, or from the second row before the current row, or from the third row before current row, and so on.<\/p>\n\n\n\n<p>The <code>LAG()<\/code> function can be very useful for calculating the difference between the current row and the previous row.<\/p>\n\n\n\n<p>The following illustrates the syntax of the <code>LAG()<\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">LAG(return_value &#91;,offset&#91;, default_value ]]) OVER (\n    PARTITION BY expr1, expr2,...\n\tORDER BY expr1 &#91;ASC | DESC], expr2,...\n)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Let&#8217;s examine each element of the <code>LAG()<\/code> function in more detail.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='return_value'>return_value <a href=\"#return_value\" class=\"anchor\" id=\"return_value\" title=\"Anchor for return_value\">#<\/a><\/h3>\n\n\n\n<p>The return value based on the specified offset. It can be a column of the row at a given offset from the current row.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='offset'>offset <a href=\"#offset\" class=\"anchor\" id=\"offset\" title=\"Anchor for offset\">#<\/a><\/h3>\n\n\n\n<p>The number of rows back from the current row from which to access data. The offset must be a non-negative integer. It defaults to one if skipped.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='default_value'>default_value <a href=\"#default_value\" class=\"anchor\" id=\"default_value\" title=\"Anchor for default_value\">#<\/a><\/h3>\n\n\n\n<p>If the preceding row is not specified, <code>default_value<\/code> is returned. For example, when the offset is 2, the return value from the first row is <code>default_value<\/code>. If <code>default_value<\/code> is not given and no preceding row found, NULL is returned by default.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='partition-by-clause'>PARTITION BY clause <a href=\"#partition-by-clause\" class=\"anchor\" id=\"partition-by-clause\" title=\"Anchor for PARTITION BY clause\">#<\/a><\/h3>\n\n\n\n<p>The <code>PARTITION BY<\/code> clause organizes rows into one or more partitions to which the <code>LAG()<\/code> function is applied. The whole result is treated as a single partition if you omit the <code>PARTITION BY<\/code> clause.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='order-by-clause'>ORDER BY clause <a href=\"#order-by-clause\" class=\"anchor\" id=\"order-by-clause\" title=\"Anchor for ORDER BY clause\">#<\/a><\/h3>\n\n\n\n<p>The <code>ORDER BY<\/code> clause specifies the order of rows in each partition to which the <code>LAG()<\/code> function is applied.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='sql-lag-function-example'>SQL LAG() function example <a href=\"#sql-lag-function-example\" class=\"anchor\" id=\"sql-lag-function-example\" title=\"Anchor for SQL LAG() function example\">#<\/a><\/h2>\n\n\n\n<p>We will <a href=\"https:\/\/www.sqltutorial.org\/sql-create-table\/\">create a new table<\/a> named <code>basic_pays<\/code> that stores the salary history of employees:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> basic_pays (\n  employee_id <span class=\"hljs-built_in\">INT<\/span>,\n  fiscal_year <span class=\"hljs-built_in\">INT<\/span>,\n  salary <span class=\"hljs-built_in\">DECIMAL<\/span>(<span class=\"hljs-number\">10<\/span>, <span class=\"hljs-number\">2<\/span>),\n  PRIMARY <span class=\"hljs-keyword\">KEY<\/span> (employee_id, fiscal_year)\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following script <a href=\"https:\/\/www.sqltutorial.org\/sql-insert\/\">inserts<\/a> data into the <code>basic_pays<\/code> table:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted minimize:true lang:tsql decode:true\">INSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2017,24000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2017,17000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2017,17000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2017,9000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(104,2017,6000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(105,2017,4800);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(106,2017,4800);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(107,2017,4200);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(108,2017,12000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(109,2017,9000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(110,2017,8200);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(111,2017,7700);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(112,2017,7800);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(113,2017,6900);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(114,2017,11000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(115,2017,3100);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(116,2017,2900);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(117,2017,2800);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(118,2017,2600);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(119,2017,2500);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(120,2017,8000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(121,2017,8200);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(122,2017,7900);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(123,2017,6500);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(126,2017,2700);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(145,2017,14000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(146,2017,13500);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(176,2017,8600);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(177,2017,8400);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(178,2017,7000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(179,2017,6200);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(192,2017,4000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(193,2017,3900);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(200,2017,4400);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(201,2017,13000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(202,2017,6000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(203,2017,6500);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(204,2017,10000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(205,2017,12000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(206,2017,8300);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2018,25920);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2018,18190);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2018,18360);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2018,9720);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(104,2018,6060);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(105,2018,4992);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(106,2018,5040);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(107,2018,4284);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(108,2018,12360);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(109,2018,9540);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(110,2018,8692);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(111,2018,7931);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(112,2018,8580);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(113,2018,7107);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(114,2018,11440);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(115,2018,3131);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(116,2018,3161);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(117,2018,2940);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(118,2018,2652);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(119,2018,2650);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(120,2018,8800);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(121,2018,8364);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(122,2018,8611);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(123,2018,6565);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(126,2018,2808);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(145,2018,14560);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(146,2018,14580);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(176,2018,9202);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(177,2018,8988);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(178,2018,7630);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(179,2018,6448);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(192,2018,4320);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(193,2018,4173);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(200,2018,4620);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(201,2018,13000);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(202,2018,6360);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(203,2018,7085);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(204,2018,10100);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(205,2018,12360);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(206,2018,8632);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(100,2020,26179.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(101,2020,19463.3);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(102,2020,19278);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(103,2020,10206);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(104,2020,6605.4);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(105,2020,5391.36);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(106,2020,5191.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(107,2020,4498.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(108,2020,13472.4);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(109,2020,9826.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(110,2020,9561.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(111,2020,8248.24);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(112,2020,9352.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(113,2020,7107);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(114,2020,12012);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(115,2020,3224.93);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(116,2020,3287.44);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(117,2020,3175.2);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(118,2020,2864.16);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(119,2020,2782.5);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(120,2020,9152);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(121,2020,8531.28);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(122,2020,8697.11);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(123,2020,6630.65);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(126,2020,2920.32);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(145,2020,16016);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(146,2020,14871.6);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(176,2020,9938.16);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(177,2020,9167.76);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(178,2020,7858.9);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(179,2020,6641.44);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(192,2020,4406.4);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(193,2020,4339.92);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(200,2020,4712.4);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(201,2020,14040);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(202,2020,6614.4);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(203,2020,7155.85);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(204,2020,10908);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(205,2020,12730.8);\nINSERT INTO basic_pays(employee_id, fiscal_year,salary) VALUES(206,2020,8890.96);\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id='using-sql-lag-function-over-partitions-example'>Using SQL LAG() function over partitions example <a href=\"#using-sql-lag-function-over-partitions-example\" class=\"anchor\" id=\"using-sql-lag-function-over-partitions-example\" title=\"Anchor for Using SQL LAG() function over partitions example\">#<\/a><\/h3>\n\n\n\n<p>The following statement returns both the current and previous year&#8217;s salary of all employees:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span>\n  employee_id,\n  fiscal_year,\n  salary,\n  LAG(salary) <span class=\"hljs-keyword\">OVER<\/span> (\n    <span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span>\n      employee_id\n    <span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span>\n      fiscal_year\n  ) previous_salary\n<span class=\"hljs-keyword\">FROM<\/span>\n  basic_pays;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following is the partial output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"320\" height=\"377\" src=\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png\" alt=\"SQL LAG Function Over Partition Example\" class=\"wp-image-1776\" title=\"SQL LAG Function Over Partition Example\" srcset=\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png 320w, https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example-255x300.png 255w\" sizes=\"auto, (max-width: 320px) 100vw, 320px\" \/><\/figure>\n\n\n\n<p>In this example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, the <code>PARTITION BY<\/code> clause divided the result set into groups by employee ID.<\/li>\n\n\n\n<li>Second, for each group, the <code>ORDER BY<\/code> clause sorted the rows by fiscal year in ascending order.<\/li>\n\n\n\n<li>Third, <code>LAG()<\/code> function applied to the row of each group independently. The first row in each group was <code>NULL<\/code> because there was no previous year&#8217;s salary. The second and third row gots the salary from the first and second row and populated them into the <code>previous_salary<\/code> column.<\/li>\n<\/ul>\n\n\n\n<p>You can find the YoY salary increment by using the following query:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n\temployee_id, \n\tfiscal_year, \n\tsalary,\n\tprevious_salary,\n\t<span class=\"hljs-keyword\">CONCAT<\/span>(<span class=\"hljs-keyword\">ROUND<\/span>(( salary - previous_salary ) * <span class=\"hljs-number\">100<\/span> \/previous_salary,<span class=\"hljs-number\">0<\/span>),<span class=\"hljs-string\">'%'<\/span>)  YoY\n<span class=\"hljs-keyword\">FROM<\/span>\n\t( <span class=\"hljs-keyword\">SELECT<\/span> \n\t\temployee_id, \n\t\tfiscal_year, \n\t\tsalary,\n\t\tLAG(salary,<span class=\"hljs-number\">1<\/span>,<span class=\"hljs-number\">0<\/span>) <span class=\"hljs-keyword\">OVER<\/span> (\n\t\t\t<span class=\"hljs-keyword\">PARTITION<\/span> <span class=\"hljs-keyword\">BY<\/span> employee_id \n\t\t\t<span class=\"hljs-keyword\">ORDER<\/span> <span class=\"hljs-keyword\">BY<\/span> fiscal_year) previous_salary\n\t<span class=\"hljs-keyword\">FROM<\/span>\n\t\tbasic_pays\n\t) t;    <\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The following picture shows the partial output:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"369\" height=\"322\" src=\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-YoY-example.png\" alt=\"SQL LAG Function YoY example\" class=\"wp-image-1778\" title=\"SQL LAG Function YoY example\" srcset=\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-YoY-example.png 369w, https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-YoY-example-300x262.png 300w\" sizes=\"auto, (max-width: 369px) 100vw, 369px\" \/><\/figure>\n\n\n\n<p>In this tutorial, you have learned how to use the SQL&nbsp;<code>LAG()<\/code> function to access data of the previous row from the current row.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='databases'>Databases <a href=\"#databases\" class=\"anchor\" id=\"databases\" title=\"Anchor for Databases\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.pgtutorial.com\/postgresql-window-functions\/postgresql-lag\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL LAG() function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.oracletutorial.com\/oracle-analytic-functions\/oracle-lag\/\" target=\"_blank\" rel=\"noreferrer noopener\">Oracle LAG() function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-window-functions\/sql-server-lag-function\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server LAG() function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-lag\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQLite LAG() function<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-lag-function\/\" target=\"_blank\" rel=\"noreferrer noopener\">MySQL LAG() function<\/a><\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"1774\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\"\n\t\t\t\tdata-post-title=\"SQL LAG Function\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"1774\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\"\n\t\t\t\tdata-post-title=\"SQL LAG Function\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1707,"menu_order":1,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1774","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL LAG() Function Explained By Practical Examples<\/title>\n<meta name=\"description\" content=\"This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL LAG() Function Explained By Practical Examples\" \/>\n<meta property=\"og:description\" content=\"This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-20T09:16:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png\" \/>\n\t<meta property=\"og:image:width\" content=\"320\" \/>\n\t<meta property=\"og:image:height\" content=\"377\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\",\"url\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\",\"name\":\"SQL LAG() Function Explained By Practical Examples\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltutorial.org\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png\",\"datePublished\":\"2018-09-10T15:05:15+00:00\",\"dateModified\":\"2025-01-20T09:16:37+00:00\",\"description\":\"This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#primaryimage\",\"url\":\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png\",\"contentUrl\":\"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png\",\"width\":320,\"height\":377,\"caption\":\"SQL LAG Function Over Partition Example\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltutorial.org\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Window Functions\",\"item\":\"https:\/\/www.sqltutorial.org\/sql-window-functions\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL LAG Function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltutorial.org\/#website\",\"url\":\"https:\/\/www.sqltutorial.org\/\",\"name\":\"SQL Tutorial\",\"description\":\"An Interactive SQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltutorial.org\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL LAG() Function Explained By Practical Examples","description":"This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/","og_locale":"en_US","og_type":"article","og_title":"SQL LAG() Function Explained By Practical Examples","og_description":"This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.","og_url":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/","og_site_name":"SQL Tutorial","article_modified_time":"2025-01-20T09:16:37+00:00","og_image":[{"width":320,"height":377,"url":"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/","url":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/","name":"SQL LAG() Function Explained By Practical Examples","isPartOf":{"@id":"https:\/\/www.sqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#primaryimage"},"image":{"@id":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png","datePublished":"2018-09-10T15:05:15+00:00","dateModified":"2025-01-20T09:16:37+00:00","description":"This tutorial shows you how to use the SQL\u00a0LAG() function to access data of the previous row from the current row.","breadcrumb":{"@id":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#primaryimage","url":"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png","contentUrl":"https:\/\/www.sqltutorial.org\/wp-content\/uploads\/2018\/09\/SQL-LAG-Function-Over-Partition-Example.png","width":320,"height":377,"caption":"SQL LAG Function Over Partition Example"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-lag\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"SQL Window Functions","item":"https:\/\/www.sqltutorial.org\/sql-window-functions\/"},{"@type":"ListItem","position":3,"name":"SQL LAG Function"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltutorial.org\/#website","url":"https:\/\/www.sqltutorial.org\/","name":"SQL Tutorial","description":"An Interactive SQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages\/1774","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/comments?post=1774"}],"version-history":[{"count":0,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages\/1774\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages\/1707"}],"wp:attachment":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/media?parent=1774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}