If you’re getting error 10753 when using a window function in SQL Server, it’s probably because you’re calling the function without an OVER clause.
When using the DENSE_RANK() function, the error message reads “The function ‘DENSE_RANK’ must have an OVER clause”.
The DENSE_RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, add an OVER clause to the DENSE_RANK() function.
Example of Error
Here’s an example of code that produces the error:
SELECT
VendorId,
ProductName,
ProductPrice,
DENSE_RANK( )
FROM Products;Result:
Msg 10753, Level 15, State 3, Line 5 The function 'DENSE_RANK' must have an OVER clause.
I got the error because I called the DENSE_RANK() function without an OVER clause.
Solution
To fix this issue, simply add an OVER clause to the DENSE_RANK() function:
SELECT
VendorId,
ProductName,
ProductPrice,
DENSE_RANK( ) OVER (
ORDER BY ProductPrice
) AS DENSE_RANK
FROM Products;Result:
VendorId ProductName ProductPrice DENSE_RANK -------- ------------------------------- ------------ ---------- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 1 1003 Hammock 10 2 1001 Long Weight (green) 11.99 3 1004 Tea Pot 12.45 4 1001 Long Weight (blue) 14.75 5 1001 Left handed screwdriver 25.99 6 1001 Right handed screwdriver 25.99 6 1002 Sledge Hammer 33.49 7 1003 Straw Dog Box 55.99 8 1003 Chainsaw 245 9
This time we got the expected result without error.
Here I also included an ORDER BY clause in the OVER clause. That’s because the OVER clause must have an ORDER BY clause. If we omit the ORDER BY clause we’ll get another error.