FIRST_VALUE, LAST_VALUE

Another cool function (actually two of them) introduced in MS SQL 2022 are FIRST_VALUE and LAST_VALUE.

They return the first/last value in an ordered set of values. It doesn't make sense to have a blog post for every of this two function's because they are basically the same, the only difference is that one return the first (let's say minimum) and the second the last value (let's say maximum).

Syntax:

FIRST_VALUE/LAST_VALUE ([scalar_expression]) [IGNORE NULLS | RESPECT NULLS] OVER ([partition_by_clause] order_by_clause [rows_range_clause])

  • scalar_expression - the value to be returned. scalar_expression can be a column, subquery, or other arbitrary expression that results in a single value. Other analytic functions aren't permitted.
  • IGNORE NULLS | RESPECT NULLS:
    • IGNORE NULLS - Ignore null values in the dataset when computing the first value over a partition.
    • RESPECT NULLS - Respect null values in the dataset when computing first value over a partition. RESPECT NULLS is the default behavior if a NULLS option isn't specified.
  • OVER - The partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.

FIRST_VALUE/LAST_VALUE return the same type as scalar_expression.

The easiest way to figure out what this functions are is to see them in one example. You will notice two things, first a more readable (smaller) SQL code. And second a small performance benefit.

Example:

Let's try to retrieve employees by department and pay grade, when they were hired, the minimum and last date of employment in that department.

-- Without new function's
;WITH CTE AS (
		SELECT MAX(e1.HireDate) AS LastHireDate, MIN(e1.HireDate) AS FirstHireDate, edh1.Department, eph1.Rate
		FROM HumanResources.vEmployeeDepartmentHistory AS edh1 
		INNER JOIN HumanResources.EmployeePayHistory AS eph1   
		ON eph1.BusinessEntityID = edh1.BusinessEntityID  
		INNER JOIN HumanResources.Employee AS e1  
		ON e1.BusinessEntityID = edh1.BusinessEntityID
		GROUP BY
		edh1.Department, eph1.Rate
)
SELECT 
	edh.Department, edh.LastName, eph.Rate, e.HireDate
	, CTE.FirstHireDate, CTE.LastHireDate
FROM 
	HumanResources.vEmployeeDepartmentHistory AS edh  
	INNER JOIN HumanResources.EmployeePayHistory AS eph    
		ON eph.BusinessEntityID = edh.BusinessEntityID  
	INNER JOIN HumanResources.Employee AS e  
		ON e.BusinessEntityID = edh.BusinessEntityID
	LEFT JOIN CTE ON CTE.Department = edh.Department AND CTE.Rate = eph.Rate
ORDER BY edh.Department, eph.Rate;    

-- With new function's
SELECT 
	edh.Department, edh.LastName, eph.Rate, e.HireDate
    , FIRST_VALUE(e.HireDate) OVER (PARTITION BY edh.Department ORDER BY eph.Rate) AS FirsttHireDate  
	, LAST_VALUE(e.HireDate) OVER (PARTITION BY edh.Department ORDER BY eph.Rate) AS LastHireDate  
FROM 
	HumanResources.vEmployeeDepartmentHistory AS edh  
	INNER JOIN HumanResources.EmployeePayHistory AS eph    
		ON eph.BusinessEntityID = edh.BusinessEntityID  
	INNER JOIN HumanResources.Employee AS e  
		ON e.BusinessEntityID = edh.BusinessEntityID
ORDER BY edh.Department, eph.Rate;  

Statistic's:

-- Without new function's
Table 'Worktable'. Scan count 0, logical reads 0...
Table 'Workfile'. Scan count 0, logical reads 0...
Table 'Person'. Scan count 0, logical reads 1124...
Table 'EmployeePayHistory'. Scan count 2, logical reads 8...
Table 'EmployeeDepartmentHistory'. Scan count 32, logical reads 64...
Table 'Department'. Scan count 2, logical reads 4...
Table 'Employee'. Scan count 2, logical reads 18...

 SQL Server Execution Times:
   CPU time = 16 ms

-- With new function's
Table 'Worktable'. Scan count 108, logical reads 1523...
Table 'Person'. Scan count 0, logical reads 1031...
Table 'Workfile'. Scan count 0, logical reads 0...
Table 'EmployeePayHistory'. Scan count 1, logical reads 4...
Table 'EmployeeDepartmentHistory'. Scan count 16...
Table 'Department'. Scan count 1, logical reads 2...
Table 'Employee'. Scan count 1, logical reads 9...

 SQL Server Execution Times:
   CPU time = 0 ms

And the execution plan:

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (2.25 kb)