
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)