If you’re getting an error that reads “NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified” in SQL Server, it’s probably because you’re trying to sort the results of a query that uses the NEXT VALUE FOR function without using the OVER clause.
If we want to sort the results of a query that uses the NEXT VALUE FOR function, we need to do it inside an OVER clause.
Therefore, we can fix this issue by rewriting the query so that the ORDER BY clause is inside an OVER clause.
Example of Error
Here’s an example of code that produces the error:
SELECT
NEXT VALUE FOR Sequence1 AS ArtistId,
ArtistName
FROM Artists
ORDER BY ArtistName;Result:
Msg 11723, Level 15, State 1, Line 2 NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.
I got this error because I tried to use the ORDER BY clause to sort the results, but this clause isn’t supported in that position.
Solution
We can fix the above issue by modifying our query to include an OVER clause, then including the ORDER BY sub clause inside that.
SELECT
NEXT VALUE FOR Sequence1 OVER (ORDER BY ArtistName) AS ArtistId,
ArtistName
FROM Artists;Result:
ArtistId ArtistName -------- ---------------------- 1 AC/DC 2 Allan Holdsworth 3 Birds of Tokyo 4 Black Sabbath 5 Bodyjar 6 Buddy Rich 7 Carabao 8 Devin Townsend 9 Iron Maiden 10 Jim Reeves 11 Karnivool 12 Lit 13 Maroon 5 14 Michael Learns to Rock 15 The Script 16 Tom Jones 16 row(s) returned