SQL LAG( ) Function

The LAG () Function is a Value Window Function used to fetch the previous row data along with the current row data of the specified offset, in other words, it is said as the LAG() Function access data of the previous row or the row before the previous row.

These LAG Functions are very useful in case we need to compare the value of the current row with the value of the previous row.

• Syntax for LAG( ) Function – 

LAG(return_value,Offset,Default_value) OVER ([PARTITION BY expression,..] ORDER BY column1,column2,..ASC|DESC);

Here we have some terms in the syntax, let’s have a look at them –

• return_value – The return_value is the value of the previous row of the given offset.
• offset – The offset is the no. of rows back from the current row. This can be an expression or column which should be a positive integer.
• default – This value is used when the offset reaches beyond the scope of the partition. It is NULL if no value is specified.

Demo Table – 

Product Table

Example – Consider the above Product Table and execute a query to fetch the sales from the previous row along with the current row value using the LAG () Function.

SELECT Category,Region,City,[Country/Region],Sales , LAG (Sales,1,-1) OVER(PARTITION BY Category ORDER BY Sales ) AS LAG FROM Products;

In the above example, we have defined the default value as -1, so whenever the number of rows goes beyond the first row in the dataset, it will show -1 for that row.

Other SQL topics to check out:

• What is RDBMS (Relational Database Management System)?
• SQL SELECT Statement
• SQL INSERT INTO SELECT Statement
• SQL ALL Operator
• SQL WHERE Clause
• SQL AND Operator
• SQL OR Operator

Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.