LEAD() LAG() in MySQL

over() & partition window

over() with rank() 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.
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
6Alex JohnFour55male55
10Big JohnFour55female60
4Krish StarFour60female60
5John MikeFour60female69
21Babby JohnFour69female75
1John DeoFour75female88
15Tade RowFour88male88
16GimmyFour88male88
31 Marry ToeeyFour88maleNULL

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
10Big JohnFour55female60
4Krish StarFour60female60
5John MikeFour60female69
21Babby JohnFour69female75
1John DeoFour75femaleNULL
6Alex JohnFour55male88
15Tade RowFour88male88
16GimmyFour88male88
31 Marry ToeeyFour88maleNULL

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
6Alex JohnFour55maleNULL
10Big JohnFour55female55
4Krish StarFour60female55
5John MikeFour60female60
21Babby JohnFour69female60
1John DeoFour75female69
15Tade RowFour88male75
16GimmyFour88male88
31 Marry ToeeyFour88male88

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 JohnFour55femaleNULL
4Krish StarFour60female55
5John MikeFour60female60
21Babby JohnFour69female60
1John DeoFour75female69
6Alex JohnFour55maleNULL
15Tade RowFour88male55
16GimmyFour88male88
31Marry ToeeyFour88male88

Over & partition window RANK() Sum Multiple column


plus2net.com

Post your comments , suggestion , error , requirements etc here





SQL Video Tutorials










We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer