SQL Standard

SQL Lag

The SQL lag() function allows you to access the previous row from the current row at a specific offset. In short, the lag() function allows you to access the previous row from the current one. By specifying the offset value, you can access the previous 1, 2, 3, etc., rows from the current one.

It is the opposite of the lead() function which allows you to access subsequent rows.

SQL Lag()

The function syntax is as follows:

LAG(value_expression, offset [, default])
OVER (
   [PARTITION BY partition_expression]
   ORDER BY sort_expression [ASC | DESC]
);

The following are the supported arguments:

  • value_expression – It specifies the return value of the preceding row. The expression must evaluate to a single value.
  • offset – It specifies how many rows backward from the current row to access.
  • default – It sets the default value if the offset is outside the scope of the partition. By default, the value is set to NULL.
  • Partition by – It specifies how to partition the data.
  • Order by – It sets the order format for the rows in each partition.

Sample Data Setup

Before we dive into the workings of the lag() function, let us start by setting up a basic table for demonstration purposes.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    category VARCHAR(255),
    price DECIMAL(10, 2),
    quantity INT,
    expiration_date DATE,
    barcode BIGINT
);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Chef Hat 25cm',
'bakery',
24.67,
57,
'2023-09-09',
2854509564204);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Quail Eggs - Canned',
'pantry',
17.99,
67,
'2023-09-29',
1708039594250);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Coffee - Egg Nog Capuccino',
'bakery',
92.53,
10,
'2023-09-22',
8704051853058);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Pear - Prickly',
'bakery',
65.29,
48,
'2023-08-23',
5174927442238);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Pasta - Angel Hair',
'pantry',
48.38,
59,
'2023-08-05',
8008123704782);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Wine - Prosecco Valdobiaddene',
'produce',
44.18,
3,
'2023-03-13',
6470981735653);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Pastry - French Mini Assorted',
'pantry',
36.73,
52,
'2023-05-29',
5963886298051);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Orange - Canned, Mandarin',
'produce',
65.0,
1,
'2023-04-20',
6131761721332);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Pork - Shoulder',
'produce',
55.55,
73,
'2023-05-01',
9343592107125);

insert
    into
    products (product_name,
    category,
    price,
    quantity,
    expiration_date,
    barcode)
values ('Dc Hikiage Hira Huba',
'produce',
56.29,
53,
'2023-04-14',
3354910667072);

With the data creation and setup complete, let us proceed to discuss a few examples.

Example 1: Basic Usage

In this case, we have access to a “products” table that contains the product information. Suppose we want to get the previous barcode from the current row.

We can use the lag() function as follows:

select
    product_name,
    price,
    lag(barcode) over (partition by category
order by
    price asc) as previous_item
from
    products p;

The given code partitions the data based on the category. It then fetches the previous barcode in the partition using the lag() function.

The resulting output is as follows:

Example 2: Setting the Default Value

If there is no previous row in a specific column (out of bound), the function sets the value to NULL as shown in the previous example.

To set a default value for any out-of-scope access, we can do the following:

select
    product_name,
    price,
    lag(barcode, 1, 'N/A') over (partition by category
order by
    price asc) as previous_item
from
    products p;

We set the default value to “N/A”. This should replace any out-of-bound value as shown in the output:

Example 3: Custom Offset Value

Suppose you want to access the previous two rows from the current row. We can do that by setting the offset value to 2.

An example query is illustrated in the following:

select
    product_name,
    price,
    lag(barcode, 2, 'N/A') over (partition by category
order by
    price asc) as previous_item
from
    products p;

Output:

This query returns the previous two rows in each partition.

Conclusion

In this tutorial, we learned how to work with the lag() function to get the previous item from the current row.

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