sql SQL Lead Function

[Linux Hint]

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

It is the opposite of the lag() function which allows you to access the previous rows.

SQL Lead() Function

The function syntax is as follows:

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

The following are the supported arguments:

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

Sample Data Setup

Before we dive into the workings of the lead() function, let us start by setting up the 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);

Example 1:

In this case, we have access to the “products” table which contains the product information. Suppose we want to get the next bar code from the current row.

We can use the lead() function as follows:

Suppose we have a table that contains an employee information as follows:

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

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

The resulting output is as follows:

Example 2:

If there is no next 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,
    lead(barcode, 1, 'N/A') over (partition by category
order by
    price asc) as next_item
from
    products p;

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

NOTE: Setting the offset to 1 is similar to not specifying any value.

Example 3:

Suppose you want to access the next 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,
    lead(barcode, 2, 'N/A') over (partition by category
order by
    price asc) as next_item
from
    products p;

This query returns the next two rows in each partition as shown in the following:

There you have it!

Conclusion

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

View the full article