FIRST_VALUE (T-SQL)

Returns the first value in an ordered set of values in SQL Server 2012
Syntax

FIRST_VALUE ( [scalar_expression ) 

    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) 

Examples

A.Using FIRST_VALUE over a query result set

The following example uses FIRST_VALUE to return the name of the product that is the least expensive in a given product category.

SELECT RepId
   ,ProductId
   ,ProductName
   ,ProductType
   ,Price
 ,FIRST_VALUE (ProductName) over (Order by Price desc) as TopProduct
 FROM work.dbo.Product

Here is the result set.




B.Using FIRST_VALUE over partitions

The following example uses FIRST_VALUE to return the employee with the   fewest number of vacation hours compared to other employees with the     same job title. The PARTITION BY clause partitions the employees by job   title and the FIRST_VALUE function is applied to each partition independently. The ORDER BY clause specified in the OVER clause determines the logical order in which the FIRST_VALUE function is applied to the rows in each       partition. The ROWS UNBOUNDED PRECEDING clause specifies the starting point of the window as the first row of the partition.


SELECT RepId
   ,ProductId
   ,ProductName
   ,ProductType
   ,Price
   ,FIRST_VALUE (ProductName) OVER (PARTITION BY ProductType ORDER BY Price) AS TopProduct 
 FROM work.dbo.Product
 ORDER BY ProductType

Here is a partial result set.