SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lead - 2


Lead - 2

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25453 Visits: 12494
mbova407 (11/12/2012)
Thanks for the question

Will someone please explain example C in english

CREATE TABLE T (a int, b int, c int);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;



I am very confused over the first parameter 2*c


It's very easy: 2*c is just what it says - the value in column c multiplied by 2. Which row supplies the value of c that is to be multiplied by 2? The row that is b*(select min(b)) rows ahead of the current row (the value of b used for that is of course the value in the current row; if 2*c calculated by those rules is NULL, the value -c/2.0 (where this time c is taken from the current row) is to be (implicitly converted to an integer and) used instead of that NULL.

More generally, the three arguments of LEAD are treated as follows:
The second and third arguments are expressions where any unqualified row names (or row names using the source table & schama names as qualifers) that are not bound in an aggregate refer to the current row. The first argument is an expression where those unbound column names refer to the row that leads the current row by the number which the second argument evaluates to. The third argument doesn't need to be evaluated unless evaluating the second argument and then the first argument leads to NULL for the first argument, but there's no guarantee that it won't be evaluated even when its value isn't needed. The other two arguments always have to be evaluated, and the first argument can't be evaluated until the second argument has been evaluated, because until then one doesn't know which row the free column names in it refer to.

Tom

Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 513
L' Eomot Inversé (11/12/2012)
mbova407 (11/12/2012)
Thanks for the question

Will someone please explain example C in english

CREATE TABLE T (a int, b int, c int);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);

SELECT b, c,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;



I am very confused over the first parameter 2*c


It's very easy: 2*c is just what it says - the value in column c multiplied by 2. Which row supplies the value of c that is to be multiplied by 2? The row that is b*(select min(b)) rows ahead of the current row (the value of b used for that is of course the value in the current row; if 2*c calculated by those rules is NULL, the value -c/2.0 (where this time c is taken from the current row) is to be (implicitly converted to an integer and) used instead of that NULL.

More generally, the three arguments of LEAD are treated as follows:
The second and third arguments are expressions where any unqualified row names (or row names using the source table & schama names as qualifers) that are not bound in an aggregate refer to the current row. The first argument is an expression where those unbound column names refer to the row that leads the current row by the number which the second argument evaluates to. The third argument doesn't need to be evaluated unless evaluating the second argument and then the first argument leads to NULL for the first argument, but there's no guarantee that it won't be evaluated even when its value isn't needed. The other two arguments always have to be evaluated, and the first argument can't be evaluated until the second argument has been evaluated, because until then one doesn't know which row the free column names in it refer to.



Thanks for the effort but I am still missing it.

So for the first row C=-3 b=1 (and min b in the table is 1)

so -3*2=-6 the offset is 1*1
So Lead(-6,1,-3/2) = 8?
----------------------------------

Actually I just got it its not Lead(c*2,b*min(b),-c/2) but the value to be returned based on the specified offset

But it is Lead(C value of the row + the offset (not the current row), the calculated offset of the current row value of B, default to the current row value of C if the first parameter is null)
Mike Is Here
Mike Is Here
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 513
The first argument is an expression where those unbound column names refer to the row that leads the current row by the number which the second argument evaluates to.



That is what you said
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64743 Visits: 18570
Another solid question Ron.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Andre Ranieri
Andre Ranieri
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 379
Cool question, this makes me want to get my hands on a SQL 2012 server Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search