Fix Error “The function ‘LEAD’ must have an OVER clause” in SQL Server

by

If you’re getting SQL Server error 10753 that reads “The function ‘LEAD’ must have an OVER clause”, it’s probably because you’re calling the LEAD() function without an OVER clause.

The LEAD() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, be sure to include an OVER clause when calling the LEAD() function.

Example of Error

Here’s an example of code that produces the error:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LEAD( ProductPrice )
FROM Products;

Result:

Msg 10753, Level 15, State 1, Line 5
The function 'LEAD' must have an OVER clause.

Here I called the LEAD() function without an OVER clause, which resulted in an error.

Solution

To fix this issue, simply add an OVER clause to the LEAD() function:

SELECT
    VendorId,
    ProductName,
    ProductPrice,
    LEAD( ProductPrice ) OVER ( 
        ORDER BY ProductPrice 
        ) AS LEAD
FROM Products;

Result:

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

This time we got the expected result.

It’s important to remember that the OVER clause must have an ORDER BY clause. Omitting this will cause a different error.