|
|
Error Message
Server: Msg 11723, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a
statement that contains an ORDER BY clause unless the
OVER clause is specified.
Causes
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence object was created.
|
The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle or repeat as requested. Sequences, unlike identity columns, are not associated with tables.
A sequence is created independently of table objects by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance.
The syntax of the CREATE SEQUENCE statement is as follows:
CREATE SEQUENCE <Sequence Name> AS <integer_type>
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
To generate a sequence number from a specified sequence object, the NEXT VALUE FOR metadata function is used. Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table.
The NEXT VALUE FOR function is a non-deterministic function, and is only allowed in contexts where the number of generated sequence values is well defined. There are a lot of limitations and restrictions with the use of the NEXT VALUE FOR. The NEXT VALUE FOR is not allowed to be used in a statement that uses the ORDER BY clause unless the OVER (ORDER BY) clause of the NEXT VALUE FOR is included as well; otherwise, this error will be raised.
To illustrate how this error can be encountered, using the following sequence object definition, as well as table definition with sample data:
CREATE SEQUENCE [dbo].[ProductSequence] AS INT
START WITH 1 INCREMENT BY 1
GO
CREATE TABLE [dbo].[Product] (
[ProductID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[ProductName] VARCHAR(100),
[UnitPrice] MONEY,
[Quantity] INT
)
GO
INSERT INTO [dbo].[Product] ( [ProductName], [UnitPrice], [Quantity] )
VALUES ( 'Flat Screen TV' , 599.99 , 10 ),
( 'Microwave Oven' , 199.99 , 20 ),
( 'Washing Machine', 449.99 , 8 )
GO
The following SELECT statement tries to return all rows from the [dbo].[Product] table together with a rank of that product assigned based on the unit price having the most expensive product getting a rank of 1 and so on.
SELECT [ProductName], [UnitPrice], [Quantity],
NEXT VALUE FOR [dbo].[ProductSequence] AS [Rank]
FROM [dbo].[Product]
ORDER BY [UnitPrice] DESC
GO
Unfortunately, the following error message is encountered:
Msg 11723, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless
the OVER clause is specified.
Solution / Work Around:
To avoid this error, as the message suggest, when using an ORDER BY clause in a statement that includes the NEXT VALUE FOR function, the OVER clause needs to be specified as well in the NEXT VALUE FOR function. Here’s an updated version of the SELECT statement earlier which includes the OVER (ORDER BY) clause and therefore avoiding this error message:
SELECT [ProductName], [UnitPrice], [Quantity],
NEXT VALUE FOR [dbo].[ProductSequence] OVER (ORDER BY [UnitPrice] DESC) AS [Rank]
FROM [dbo].[Product]
ORDER BY [UnitPrice] DESC
GO
Here’s the result of this SELECT statement using the sample data provided earlier:
ProductName UnitPrice Quantity Rank
---------------- ---------- --------- -----
Flat Screen TV 599.99 10 1
Washing Machine 449.99 8 2
Microwave Oven 199.99 20 3
Executing this SELECT statement again will generate a different result as the sequence object has already been used and updated, as can be seen in the following result set:
ProductName UnitPrice Quantity Rank
---------------- ---------- --------- -----
Flat Screen TV 599.99 10 4
Washing Machine 449.99 8 5
Microwave Oven 199.99 20 6
If the purpose of using the sequence object is to assign a rank for each row in a result set based on a certain ordering, the RANK ranking function can be used instead of a sequence object. The RANK ranking function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. Here’s how the SELECT statement will look like using the RANK ranking function instead of the sequence object:
SELECT [ProductName], [UnitPrice], [Quantity],
RANK() OVER (ORDER BY [UnitPrice] DESC) AS [Rank]
FROM [dbo].[Product]
ORDER BY [UnitPrice] DESC
GO
Here’s the result of this updated SELECT statement that uses the RANK ranking function.
ProductName UnitPrice Quantity Rank
---------------- ---------- --------- -----
Flat Screen TV 599.99 10 1
Washing Machine 449.99 8 2
Microwave Oven 199.99 20 3
Using the RANK ranking function, if two or more rows tie for a rank, each tied rows receives the same rank. To illustrate, adding a new row to the table:
INSERT INTO [dbo].[Product] ( [ProductName], [UnitPrice], [Quantity] )
VALUES ( 'Dishwasher' , 449.99 , 12 )
GO
Issuing the same SELECT statement earlier yields the following result set:
ProductName UnitPrice Quantity Rank
---------------- ---------- --------- -----
Flat Screen TV 599.99 10 1
Washing Machine 449.99 8 2
Dishwasher 449.99 12 2
Microwave Oven 199.99 20 4
If duplicate ranking is not desired in the output, instead of using the RANK ranking function, the ROW_NUMBER ranking function can be used. The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row of each partition. Here’s how the SELECT statement will look like using the ROW_NUMBER ranking function:
SELECT [ProductName], [UnitPrice], [Quantity],
ROW_NUMBER() OVER (ORDER BY [UnitPrice] DESC) AS [Rank]
FROM [dbo].[Product]
ORDER BY [UnitPrice] DESC
GO
And here’s the result of this updated SELECT statement that uses the ROW_NUMBER ranking function.
ProductName UnitPrice Quantity Rank
---------------- ---------- --------- -----
Flat Screen TV 599.99 10 1
Washing Machine 449.99 8 2
Dishwasher 449.99 12 3
Microwave Oven 199.99 20 4
|