Windowing functions are the best thing since sliced bread when it comes to T-SQL. Two of the functions introduced with SQL Server 2012 let you include a column from the first or last row of the results with the current row without doing a self-join. These two functions are FIRST_VALUE and LAST_VALUE. Back when I was first exploring this functionality, I had no problem getting FIRST_VALUE to work. But, even while following the documentation, I couldn’t get the correct results when using LAST_VALUE.

To use either of these functions, you must specify the column that you wish to include and an ORDER BY expression in the OVER clause. The ORDER BY expression determines which row is the “first row” and which row is the “last row.” You can also specify a partition that divides the results into smaller windows. There is one more thing you need to know to properly use these functions, something that is not obvious in the current documentation. Before I explain, take a look at how to use FIRST_VALUE with an example from AdventureWorks.

  SELECT
        CustomerID
    ,   SalesOrderID
    ,   CAST(OrderDate AS DATE) AS OrderDate
    ,   TotalDue
    ,   FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS TD
    FROM
        Sales.SalesOrderHeader
    ORDER BY
        CustomerID
    ,   SalesOrderID;

The query is ordered by CustomerID, SalesOrderID just for the convenience of viewing the results shown in Figure 1. The argument to the FIRST_VALUE function is TotalDue. The TotalDue value from the first row of the window will be pulled into each row. The window is divided up into smaller windows by CustomerID and the order of the window is determined by sorting on SalesOrderID.

Figure 1

The value 3756.989 is the very first TotalDue for CustomerID 11000, so that value is returned for the three rows. If you replace the FIRST_VALUE function with LAST_VALUE, changing nothing else, the expected value of TD in each row for CustomerID 11000 should be 2770.2682. Run the following query to see what happens.

SELECT
          CustomerID
      ,   SalesOrderID
      ,   CAST(OrderDate AS DATE) AS OrderDate
      ,   TotalDue
      ,   LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS TD
      FROM
          Sales.SalesOrderHeader
      ORDER BY
          CustomerID

When you take a look at the results in Figure 2, you will see something odd. Instead of returning the TotalDue from the last row of the partition, the value from the current row is returned. 

Figure 2

To see what is happening, you must understand the concept of framing. The frame allows you to specify a set of rows for the window that is even smaller than the partition. The default frame contains the rows starting with the first row and up to the current row. For row 1, the window is just row 1. For row 3, the window contains rows 1 to 3. When using FIRST_VALUE, the first row is included by default, so you don’t have to worry about it to get the expected results.

When using LAST_VALUE with the default frame, the window only goes up to the current row. The current row is the last row of the window. To get around this problem, you must specify the frame, in this case ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. This means that the window starts with the current row and ends at the last row of the partition. Run the next example to see how to use LAST_VALUE correctly.

SELECT
        CustomerID
    ,   SalesOrderID
    ,   CAST(OrderDate AS DATE) AS OrderDate
    ,   TotalDue
    ,   LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS TD
    FROM
        Sales.SalesOrderHeader
    ORDER BY
        CustomerID
    ,   SalesOrderID;

Figure 3 shows the results. Now that the frame is specified, the window contains the correct rows and the results are as expected.

Framing is a very important part of T-SQL window functions. If you don’t include a frame when it is supported, you may not get the results that you expect. To learn more about T-SQL window functions and framing, be sure to check out my new book “Expert T-SQL Window Functions in SQL Server".

Kathi Kellenberger is the author of Expert T-SQL Window Functions in SQL Server. Pick up a copy to learn more about solving your complex query issues with windowing functions.