MySQL lead() lag() non-aggregate functions in MySQL

Using non-aggregate function LEAD(), LAG() with OVER we can compare row value with previous and next values.

LEAD(val) OVER ([ partition_clause ] order_by)

Using student sample table.

Note : LEAD() , LAG() are available in MySQL – 8 and above only.

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

The last row value for my_lead is NULL as there is no next row to use the value.

id	name	class	mark	gender	my_lead
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 PRTITION

There are two NULL values for my_lead due to PARTITION over gender column.

SELECT id, name,class,mark,gender,
LEAD(mark) OVER(PARTITION by GENDER ORDER BY mark ) as my_lead
FROM student WHERE class='Four';

id	name	class	mark	gender	my_lead
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()

The lagging column value is displayed for comparison, for the first row NULL is shown.

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

Output

id	name	class	mark	gender	my_lead
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 with LAG()

The window is created by using gender column so NULL is present at the staring of the window ( id = 10 , id = 6 )

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

id	name	class	mark	gender	my_lead
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

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

LEAD() LAG() in MySQL

Using PRTITION

LAG()

Using PARTITION with LAG()

Subhendu Mohapatra