SQL Server Interview Question #97

What are FIRST_VALUE() and LAST_VALUE()?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

FIRST_VALUE returns the first value in the defined window frame, while LAST_VALUE returns the last value in the frame.

A frequent interview trap is LAST_VALUE: with an ORDER BY, the default frame may end at the current row rather than the final row of the partition. To obtain the last value across the entire partition, explicitly define an appropriate frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Code Example

SELECT CategoryId,
       Name,
       Price,
       FIRST_VALUE(Price) OVER
       (
           PARTITION BY CategoryId
           ORDER BY Price
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
       ) AS LowestPrice,
       LAST_VALUE(Price) OVER
       (
           PARTITION BY CategoryId
           ORDER BY Price
           ROWS BETWEEN UNBOUNDED PRECEDING
                    AND UNBOUNDED FOLLOWING
       ) AS HighestPrice
FROM dbo.Products;