Skip to content
Advertisements

SQL LEAD and LAG()

The “LEAD ()” function returns the next incoming value based on the specified offset value. If no such value exists, it returns “default”. Returns “NULL” if “default” is not entered in the function. In addition, if no [offset value] is entered in our function, default is set to 1.

The LAG () function works in the opposite way with the LEAD () function. So it returns the value before it.

SELECT cus.first_name,cus.last_name, 
LAG(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name)
FROM BikeStores.sales.customers as cus;
SQL LEAD and LAG()SQL LEAD and LAG()
SQL LEAD and LAG()

SELECT cus.first_name,cus.last_name, 
LEAD(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name)
FROM BikeStores.sales.customers as cus;
SQL LEAD and LAG()
SQL LEAD and LAG()

SELECT cus.first_name,cus.last_name, 
LAG(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name) as [LAG COL],
LEAD(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name) as [LEAD COL]
FROM BikeStores.sales.customers as cus;
SQL LEAD and LAG()
SQL LEAD and LAG()
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: