LEADing Rows

Question

LEADing Rows

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

Steve Jones - SSC Editor (9/22/2015)
sknox (9/22/2015)
Koen Verbeeck (9/22/2015)
Even though I know what LEAD does, I still got it wrong because for some reason I was thinking from "right to left". Moar caffeine!
LAG and LEAD always trip me up, because I forget what is LAGging and what is LEADing.
(Really, no pun about a LEADing question yet? I'm slightly disappointed.) 😛
I agree. I tend to think of LAG as my row is lagging what I'm looking at ahead, but it's the reverse.

I think of it in terms of where the value is returned. When you select a column, the value being displayed is returned in a row. The LAG refers to the row(s) that LAGs behind the current row and the LEAD refers to the row(s) that LEAD it. I sometimes get tripped up by leading versus lagging indicators, but if I try to remember that the data is returned in a row, it gives me the base row from which to think.

I know...thinking in rows = :sick:, but this is the best way I've found to keep track of this particular thing.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Marcia J SSCertifiable Points: 5649 More actions · Answer 2

Iwas Bornready (9/22/2015)
Thanks for the question. Of course I can't use these because we are still on 2008.

Same for me. And there are so many places where this would come in handy!

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 3

Thanks for this easy question as I remember I have chosen the bad answer on a previous QOD about the same question . After this error , I have studied the LEAD and LAG functions.

To remember the difference between LAG and LEAD , I used a method which is working for french-speaking persons based on the second letter :

A (LAG) ==>avant ( before in English )

E (LEAD) ==> ensuite ( afterwards , next in English )

Maybe stupid but efficient.

paul s-306273 SSChampion Points: 10727 More actions · Answer 4

...thought MS had made it reasonably easy to determine what LAG and LEAD do. For those using the language it's just plain English.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

paul s-306273 (9/23/2015)
...thought MS had made it reasonably easy to determine what LAG and LEAD do. For those using the language it's just plain English.

I still confuse it from time to time, because I usually imagine the "window" as a stack, not a line. And with a stack (top to bottom) it's a bit harder to say what lags. The one above or below? (it's above)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ed Wagner SSC Guru Points: 287024 More actions · Answer 6

Koen Verbeeck (9/23/2015)
paul s-306273 (9/23/2015)
...thought MS had made it reasonably easy to determine what LAG and LEAD do. For those using the language it's just plain English.
I still confuse it from time to time, because I usually imagine the "window" as a stack, not a line. And with a stack (top to bottom) it's a bit harder to say what lags. The one above or below? (it's above)

What if, in that stack, each row has a pointer. The row you're on is row zero. Is that helpful?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Ed Wagner (9/23/2015)
Koen Verbeeck (9/23/2015)
paul s-306273 (9/23/2015)
...thought MS had made it reasonably easy to determine what LAG and LEAD do. For those using the language it's just plain English.
I still confuse it from time to time, because I usually imagine the "window" as a stack, not a line. And with a stack (top to bottom) it's a bit harder to say what lags. The one above or below? (it's above)
What if, in that stack, each row has a pointer. The row you're on is row zero. Is that helpful?

Maybe 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 8

paul s-306273 (9/23/2015)
...thought MS had made it reasonably easy to determine what LAG and LEAD do. For those using the language it's just plain English.

The rows that lead the current row are the subsequent rows, ie the rows that come after it. If leading means coming behind, the SQL use of LEAD is indeed just plain English, but it's news to me that it means that. In fact in English it normally means the opposite of that.

Of course that's no excuse for not knowing what LEAD and LAG do in SQL - it is indeed just plain SQL, but despite its original name (Structured English Query Language) plain SQL is very different from plain English.

Tom

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 9

TomThomson (9/27/2015)
paul s-306273 (9/23/2015)
...thought MS had made it reasonably easy to determine what LAG and LEAD do. For those using the language it's just plain English.
The rows that lead the current row are the subsequent rows, ie the rows that come after it. If leading means coming behind, the SQL use of LEAD is indeed just plain English, but it's news to me that it means that. In fact in English it normally means the opposite of that.
Of course that's no excuse for not knowing what LEAD and LAG do in SQL - it is indeed just plain SQL, but despite its original name (Structured English Query Language) plain SQL is very different from plain English.

+1 you have provided an explanation easy to understand ( and more important to remember )

==> Thanks ....