Lead:-New function in sql server denali

LEAD

We can get value from next subsequent row with given offset value without using a self join or CTE
We can use this function to compare  value with other row in the Table for analysis purpose



Syntax

LEAD ( scalar_expression ,offset , default )
    OVER ( Parition By,Order By)


Scalar Expression :- Any scalar expression allowed in SQL Server
Offset:- Number of rows forward from the current row
Default:- Default value if no row find at given Offset location  from current row


Example

Lets see we want to compare order quantity of each workorder with its next workorder

SELECT Lead(orderqty, 1, 0) OVER (ORDER BY workorderid) nextorderqty,

       orderqty,

       *

FROM   production.workorder

Use of Partition by function

Now we want to compare order quantity but in each ProductID

SELECT Lead(orderqty, 1, 0) OVER (partition BY productid ORDER BY workorderid) nextorderqty,

       orderqty,

       *

FROM   production.workorder

Here last order in each ProductID  will have default value 0 because it doesnt have next record in the same ProductID