MySQL LEAD() and LAG() Window Functions

LEAD() reads a value from a following row and LAG() reads a value from a previous row in the window. Use them with OVER() to compare neighboring rows without a self join.

LEAD(value_expression) OVER (ORDER BY expression)

Using student sample table.

Note : LEAD() , LAG() are available in MySQL 8.0 and later.

SELECT id, name, class, mark, gender,
       LEAD(mark) OVER (ORDER BY mark, id) AS next_mark
FROM student
WHERE class = 'Four';

The last row has NULL for next_mark because there is no following row in the window.

id	name	class	mark	gender	next_mark
6	Alex John	Four	55	male	55
10	Big John	Four	55	female	60
4	Krish Star	Four	60	female	60
5	John Mike	Four	60	female	69
21	Babby John	Four	69	female	75
1	John Deo	Four	75	female	88
15	Tade Row	Four	88	male	88
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	NULL

Using PARTITION BY

Each gender partition has its own final row, so each partition produces one NULL value for next_mark.

SELECT id, name, class, mark, gender,
       LEAD(mark) OVER (PARTITION BY gender ORDER BY mark, id) AS next_mark
FROM student
WHERE class = 'Four';

id	name	class	mark	gender	next_mark
10	Big John	Four	55	female	60
4	Krish Star	Four	60	female	60
5	John Mike	Four	60	female	69
21	Babby John	Four	69	female	75
1	John Deo	Four	75	female	NULL
6	Alex John	Four	55	male	88
15	Tade Row	Four	88	male	88
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	NULL

LAG()

LAG() returns a value from a previous row in the window. The first row in each window has no previous row, so it returns NULL.

SELECT id, name, class, mark, gender,
       LAG(mark) OVER (ORDER BY mark, id) AS previous_mark
FROM student
WHERE class = 'Four';

Output

id	name	class	mark	gender	previous_mark
6	Alex John	Four	55	male	NULL
10	Big John	Four	55	female	55
4	Krish Star	Four	60	female	55
5	John Mike	Four	60	female	60
21	Babby John	Four	69	female	60
1	John Deo	Four	75	female	69
15	Tade Row	Four	88	male	75
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	88

Using PARTITION BY with LAG()

PARTITION BY gender creates a separate window for each gender, so the first row of each partition has a NULL previous value.

SELECT id, name, class, mark, gender,
       LAG(mark) OVER (PARTITION BY gender ORDER BY mark, id) AS previous_mark
FROM student
WHERE class = 'Four';

id	name	class	mark	gender	previous_mark
10	Big John	Four	55	female	NULL
4	Krish Star	Four	60	female	55
5	John Mike	Four	60	female	60
21	Babby John	Four	69	female	60
1	John Deo	Four	75	female	69
6	Alex John	Four	55	male	NULL
15	Tade Row	Four	88	male	55
16	Gimmy	Four	88	male	88
31	Marry Toeey	Four	88	male	88

Over & partition window RANK() Sum Multiple column

All Window Functions Ranking Functions NULL Values

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here