SQL Standard

SQL Lag Function

The LAG function in SQL allows you to fetch the previous row at a specific offset from the current row. This means you can access the previous row from the current row. You can also specify a particular offset to access the second, third, and so on the previous row.

Let us discuss how we can use the lag function in SQL.

Function Syntax

The function syntax is as shown:

LAG (scalar_expression [, offset] [, DEFAULT])
OVER ( [ partition_by ] order_by ) ;

The function parameters are expressed as shown below:

  1. scalar_expression – refers to the return value based on a specified offset from the current row.
  2. Offset – defines the number of previous elements from the current row from which to access the values. By default, SQL will use an offset value of 1.
  3. Default – sets the default value if the offset value is beyond the scope of the partition. The default value is NULL.
  4. Partition by – partitions the data into one or more partitions depending on the specified condition.
  5. Order by – expression to order the data in each partition.

Example 1

Suppose we have a table containing employee data as shown:

To get the current and previous employee names, we can run the query as shown:

SELECT full_name, lag(full_name)
OVER (partition BY category ORDER BY id) AS prev_emp
FROM employees;

The code above should return the current and previous employees’ names in each partition.

The output is as shown:

Example 2

If there is no previous row in the partition, the function will return null. If we want to set a value for the non-existent rows, we can set the default parameter as shown:

SELECT full_name, lag(full_name, 1, 'missing person')
OVER (partition BY category ORDER BY id) AS prev_emp
FROM employees;

This should replace the null values with ‘missing person.’ The output will be shown below:

Example 3

Suppose we want to access two rows back from the current row? We can do that by specifying the offset as shown:

SELECT full_name, lag(full_name, 2, 'N/A')
OVER (partition BY category ORDER BY id) AS prev_emp
FROM employees;

The query above should go back to two rows and select its value.

Output:

Conclusion

In this article, you learned how to use the SQL LAG() function to access the previous row from the current row.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list