FIRST_VALUE:-New function in sql server denali

FIRST_VALUE


A new function SQL Server Denali to return first value from ordered set


FIRST_VALUE (
    OVER ( < partition_by_clause > order_by_clause < rows_range_clause > )




scalar_expression :-  A valid scalar  expression
over clause:- partition by to partition dataset based on given function if not then all rows are in a single group, order by to specify order . rows_range_clause  is new argument where we can set start and end point in the rows

Example

I want to get first orderqty from the result set

SELECT First_value (orderqty) OVER (ORDER BY workorderid) firstorderqty,

       *

FROM   production.workorder

Now we want to get first orderqty for each ProductId order by workorderid

SELECT First_value (orderqty) OVER (partition BY productid ORDER BY workorderid) firstorderqty,

       *

FROM   production.workorder