December 23, 2013 at 4:02 pm
Not crazy about this question as it is misleading. LEAD / LAG are not generally used to retrieve "all rows before this one" without doing some additional work to the query.
Really the purpose of LEAD / LAG is to provide an easy way to access 1 forward / 1 back of the current resultset without the need for a self join.
Lead: http://msdn.microsoft.com/en-us/library/hh213125.aspx
Lag: http://msdn.microsoft.com/en-us/library/hh231256.aspx
They don't really make any mention of a full result set being returned as a product of this analytic function alone.
December 24, 2013 at 11:52 am
Nice and easy one..
Thanks Steve.
December 24, 2013 at 6:51 pm
Steve Jones - SSC Editor (12/20/2013)
You would use unbounded preceding for all the rows. The title did say all the rows, but the question did not.
Steve, that's special pleading. LAG references a single row. the question says "those rows ...", not "a row ...". It also talks about an aggregate, and it's a bit odd to think of an aggregate applying to a single row. Can you give a sensible example of how LAG can be used to cause an aggregate to apply to rows (as opposed to a row)?
Tom
December 25, 2013 at 7:09 am
Good question. Thanks. I see some good conversation stemming from this question. 😉
December 26, 2013 at 10:57 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 26, 2013 at 11:24 pm
Very good question. 🙂
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply