FIRST_VALUE (Transact-SQL)
Returns the first value in an ordered set of values in SQL Server 2012.
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.
USE AdventureWorks2012;
GO
SELECT Name, ListPrice,
FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive
FROM Production.Product
WHERE ProductSubcategoryID = 37;
Here is the result set.
Name ListPrice LeastExpensive ----------------------- --------------------- -------------------- Patch Kit/8 Patches 2.29 Patch Kit/8 Patches Road Tire Tube 3.99 Patch Kit/8 Patches Touring Tire Tube 4.99 Patch Kit/8 Patches Mountain Tire Tube 4.99 Patch Kit/8 Patches LL Road Tire 21.49 Patch Kit/8 Patches ML Road Tire 24.99 Patch Kit/8 Patches LL Mountain Tire 24.99 Patch Kit/8 Patches Touring Tire 28.99 Patch Kit/8 Patches ML Mountain Tire 29.99 Patch Kit/8 Patches HL Road Tire 32.60 Patch Kit/8 Patches HL Mountain Tire 35.00 Patch Kit/8 Patches
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 is the first row of each partition.
USE AdventureWorks2012;
GO
SELECT JobTitle, LastName, VacationHours,
FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle
ORDER BY VacationHours ASC
ROWS UNBOUNDED PRECEDING
) AS FewestVacationHours
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY JobTitle;
Here is a partial result set.
JobTitle LastName VacationHours FewestVacationHours ----------------------------------- ------------------------- ------------- ------------------- Accountant Moreland 58 Moreland Accountant Seamans 59 Moreland Accounts Manager Liu 57 Liu Accounts Payable Specialist Tomic 63 Tomic Accounts Payable Specialist Sheperdigian 64 Tomic Accounts Receivable Specialist Poe 60 Poe Accounts Receivable Specialist Spoon 61 Poe Accounts Receivable Specialist Walton 62 Poe
