Sql Server Lag Function
SQL Server: LAG Function
This Oracle tutorial explains how to use the SQL Server (Transact-SQL) LAG function with syntax and examples.
Description
In SQL Server (Transact-SQL), the LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.
Syntax
The syntax for the LAG function in SQL Server (Transact-SQL) is:
LAG ( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )Parameters or Arguments
- expression
- offset
- default
- query_partition_clause
- order_by_clause
An expression that can contain other built-in functions, but can not contain any analytic functions.
Optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.
Optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.
Optional. It is used to partition the results into groups based on one or more expressions.
Optional. It is used to order the data within each partition.
Applies To
The LAG function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Example
The LAG function can be used in SQL Server (Transact-SQL).
Let's look at an example. If we had an employees table that contained the following data:
| employee_number | last_name | first_name | salary | dept_id |
|---|---|---|---|---|
| 12009 | Sutherland | Barbara | 54000 | 45 |
| 34974 | Yates | Fred | 80000 | 45 |
| 34987 | Erickson | Neil | 42000 | 45 |
| 45001 | Parker | Sally | 57500 | 30 |
| 75623 | Gates | Steve | 65000 | 30 |
And we ran the following SQL statement:
SELECT dept_id, last_name, salary,
LAG (salary,1) OVER (ORDER BY salary) AS lower_salary
FROM employees;It would return the following result:
| dept_id | last_name | salary | lower_salary |
|---|---|---|---|
| 45 | Erickson | 42000 | NULL |
| 45 | Sutherland | 54000 | 42000 |
| 30 | Parker | 57500 | 54000 |
| 30 | Gates | 65000 | 57500 |
| 45 | Yates | 80000 | 65000 |
In this example, the LAG function will sort in ascending order all of the salary values in the employees table and then return the salary that is 1 position lower in the result set since we used an offset of 1.
If we had used an offset of 2 instead, it would have returned the salary that is 2 salaries lower. If we had used an offset of 3, it would have returned the salary that is 3 lower....and so on.
Using Partitions
Now let's look at a more complex example where we use a query partition clause to return the lower salary for each employee within their own department.
Enter the following SQL statement:
SELECT dept_id, last_name, salary,
LAG (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS lower_salary
FROM employees;It would return the following result:
| dept_id | last_name | salary | lower_salary |
|---|---|---|---|
| 30 | Parker | 57500 | NULL |
| 30 | Gates | 65000 | 57500 |
| 45 | Erickson | 42000 | NULL |
| 45 | Sutherland | 54000 | 42000 |
| 45 | Yates | 80000 | 54000 |
In this example, the LAG function will partition the results by dept_id and then sort by salary as indicated by PARTITION BY dept_id ORDER BY salary. This means that the LAG function will only evaluate a salary value if the dept_id matches the current record's dept_id. When a new dept_id is encountered, the LAG function will restart its calculations and use the appropriate dept_id partition.
As you can see, the 1st record in the result set has a value of NULL for the lower_salary because it is the first record for the partition where dept_id is 30 (sorted by salary) so there is no lower salary value. This is also true for the 3rd record where the dept_id is 45.
