Fixing the Error: “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY” in SQL Server

If you’re getting an error message that reads “The function ‘LAST_VALUE’ must have an OVER clause with ORDER BY.” when using the LAST_VALUE() function in SQL Server, it’s probably because you’ve omitted the ORDER BY clause from the OVER clause.

The LAST_VALUE() function requires an OVER clause that contains an ORDER BY clause. This error happens when we include the OVER clause but not the ORDER BY clause.

To fix this error, add an ORDER BY clause to the OVER clause.

Example of Error

Here’s an example of code that results in the error:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice ) OVER (
        PARTITION BY VendorId
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LAST_VALUE
FROM Products;

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'LAST_VALUE' must have an OVER clause with ORDER BY.

The error occurred because, although I provided an OVER clause, it doesn’t contain an ORDER BY clause.

Note that simply adding an ORDER BY clause to the end of the query will not fix the issue:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice ) OVER (
        PARTITION BY VendorId
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LAST_VALUE
FROM Products
ORDER BY ProductPrice;

Result:

Msg 4112, Level 15, State 1, Line 5
The function 'LAST_VALUE' must have an OVER clause with ORDER BY.

While it’s perfectly valid to have an ORDER BY clause at the end of the query, there still needs to be one in the OVER clause.

Solution

All we need to do is add an ORDER BY clause to the OVER clause:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LAST_VALUE( ProductPrice ) OVER (
        PARTITION BY VendorId
        ORDER BY ProductPrice
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LAST_VALUE
FROM Products;

Result:

VendorId  ProductName                      ProductPrice  LAST_VALUE
--------  -------------------------------  ------------  ----------
1001      Long Weight (green)              11.99         25.99     
1001      Long Weight (blue)               14.75         25.99     
1001      Left handed screwdriver          25.99         25.99     
1001      Right handed screwdriver         25.99         25.99     
1002      Sledge Hammer                    33.49         33.49     
1003      Hammock                          10            245       
1003      Straw Dog Box                    55.99         245       
1003      Chainsaw                         245           245       
1004      Bottomless Coffee Mugs (4 Pack)  9.99          12.45     
1004      Tea Pot                          12.45         12.45     

This time the function worked as expected.

In the above examples the OVER clause contains a PARTITION BY clause, which is optional (we can omit the PARTITION BY clause if we want). The ORDER BY clause however, is required.