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








No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts