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