SQL LAG()

The LAG() window function facilitates access to previous rows based on the offset argument. It can be particularly useful when a comparison of a previous value is necessary without the use of a self join. There is a similarity to the LEAD() function with the difference being the accessible rows. LEAD() accesses subsequent rows while LAG() accesses previous rows.

Syntax

LAG (expression [, offset] [, default])
OVER ( [ partition_by ] order_by )

LAG() accepts the following parameters:

• expression - The column value which will be referenced.
• offset - A positive numeric indicator of the previous row to access that is relative to the current row. If not specified the default is 1.
• default - The value that will be returned if the offset is out of range. This is an optional argument, if not specified NULL will be returned.
• partition_by - Allows the result set to be grouped based on a column. This is an optional argument, if not specified the result set will be treated as a single group.
• order_by - Determines the order of the result set. If partition_by is specified, it will order the grouped data instead.

Example

The example below uses the LAG() function to create the new column previous_age based on values in the previous row of the Users table.

Users Table

SELECT
    first_name,
    last_name,
    age,
    LAG(age, 1) OVER (
        ORDER BY age DESC
    ) AS previous_age
FROM Users;

Copy to clipboard
Copy to clipboard

The output of the above code is a table that features a new column previous_age, which holds the values from the previous records. The first record is null because a default was not specified and the previous row would be out of range.

Using PARTITION BY Clause

This example demonstrates how to use the LAG() function to create a new column, previous_position.

The PARTITION BY employee_id clause ensures that the LAG() function operates within each group of rows that share the same employee_id. The ORDER BY promotion_date ensures the rows are processed in chronological order.

Promotions Table

SELECT
    employee_id,
    promotion_date,
    new_position,
    LAG(new_position) OVER (
        PARTITION BY employee_id
        ORDER BY promotion_date
    ) AS previous_position
FROM Promotions;

Copy to clipboard
Copy to clipboard

Within each group defined by employee_id, the previous_position column holds the value from the previous row based on promotion_date. The first record in each group is NULL because there is no preceding row.

The above code generates the following output: