If you’re getting an error that reads “NEXT VALUE FOR function does not support the PARTITION BY clause” in SQL Server, it’s probably because you’re trying to use the PARTITION BY sub clause in an OVER clause when using NEXT VALUE FOR to increment a sequence object.

In other words, the NEXT VALUE FOR function does not support the PARTITION BY sub clause of the OVER clause.

To fix this issue, either remove the PARTITION BY clause or change the statement to use another method for partitioning the results.

Example of Error

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

SELECT
  Genre,
  NEXT VALUE FOR Sequence1 OVER (PARTITION BY Genre ORDER BY AlbumId ASC) RowInGroup,
  AlbumId,
  AlbumName
FROM Albums
INNER JOIN Genres 
ON Albums.GenreId = Genres.GenreId;

Result:

Msg 11716, Level 15, State 1, Line 3
NEXT VALUE FOR function does not support the PARTITION BY clause.

In this case, I’m using NEXT VALUE FOR to increment an existing sequence object (called Sequence1). The problem is that I’m trying to use the PARTITION BY sub clause in the OVER clause.

Although the NEXT VALUE FOR function does support the OVER clause, it doesn’t support the PARTITION BY sub clause within that clause.

Solution

To fix this issue, we can either remove the PARTITION BY clause from our query, or we can rewrite the query to use some other method to achieve our desired outcome.

For example, here’s what happens when I remove the PARTITION BY clause:

SELECT
  Genre,
  NEXT VALUE FOR Sequence1 OVER (ORDER BY AlbumId ASC) RowInGroup,
  AlbumId,
  AlbumName
FROM Albums
INNER JOIN Genres 
ON Albums.GenreId = Genres.GenreId;

Result:

Genre    RowInGroup  AlbumId  AlbumName               
-------  ----------  -------  ------------------------
Rock     101         1        Powerslave              
Rock     102         2        Powerage                
Country  103         3        Singing Down the Lane   
Rock     104         4        Ziltoid the Omniscient  
Rock     105         5        Casualties of Cool      
Rock     106         6        Epicloud                
Rock     107         7        Somewhere in Time       
Rock     108         8        Piece of Mind           
Rock     109         9        Killers                 
Rock     110         10       No Prayer for the Dying 
Pop      111         11       No Sound Without Silence
Jazz     112         12       Big Swing Face          
Pop      113         13       Blue Night              
Pop      114         14       Eternity                
Pop      115         15       Scandinavia             
Pop      116         16       Long Lost Suitcase      
Pop      117         17       Praise and Blame        
Pop      118         18       Along Came Jones        
Jazz     119         19       All Night Wrong         
Jazz     120         20       The Sixteen Men of Tain 

20 row(s) returned

This particular sequence started at 101, but it could have been any number.

Another option is to swap the NEXT VALUE FOR function for something else, such as the ROW_NUMBER() function:

SELECT
  Genre,
  ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY AlbumId ASC) RowWithinPartition,
  AlbumId,
  AlbumName
FROM Albums
INNER JOIN Genres 
ON Albums.GenreId = Genres.GenreId;

Result:

Genre    RowWithinPartition  AlbumId  AlbumName               
-------  ------------------  -------  ------------------------
Country  1                   3        Singing Down the Lane   
Jazz     1                   12       Big Swing Face          
Jazz     2                   19       All Night Wrong         
Jazz     3                   20       The Sixteen Men of Tain 
Pop      1                   11       No Sound Without Silence
Pop      2                   13       Blue Night              
Pop      3                   14       Eternity                
Pop      4                   15       Scandinavia             
Pop      5                   16       Long Lost Suitcase      
Pop      6                   17       Praise and Blame        
Pop      7                   18       Along Came Jones        
Rock     1                   1        Powerslave              
Rock     2                   2        Powerage                
Rock     3                   4        Ziltoid the Omniscient  
Rock     4                   5        Casualties of Cool      
Rock     5                   6        Epicloud                
Rock     6                   7        Somewhere in Time       
Rock     7                   8        Piece of Mind           
Rock     8                   9        Killers                 
Rock     9                   10       No Prayer for the Dying 

20 row(s) returned

In this case, we were able to use the PARTITION BY clause because the ROW_NUMBER() function supports it.