SQL Server Error Messages - Msg 461

Error Message

Server: Msg 461, Level 16, State 1, Line 1
TOP operator is not allowed in the recursive part of a recursive common table expression '<CTE Name>'.

Causes

A common table expression (CTE) can be thought of as a temporary result set that is defined with the execution scope of a SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A self-referencing CTE is referred to as a recursive common table expression.

The general syntax of a recursive common table expression is as follows:

WITH <CTE Name> [ (  [, … n ] ) ]
AS (
    <CTE Query Definition – Anchor Member>
    UNION ALL
    <CTE Query Definition – Recursive Member>
)
SELECT * FROM <CTE Name>

One of the restrictions when defining a recursive common table expression is that the recursive member of the CTE cannot use the TOP operator, as suggested by the error message.

To illustrate a scenario where the TOP operator is needed in a recursive common table expression, given the following the following tables structure:

CREATE TABLE [dbo].[SalesPerson] (
    [SalesPersonID]     INT NOT NULL PRIMARY KEY,
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50),
    [SalesManagerID]    INT,
    [YTDSales]          MONEY
)
GO

ALTER TABLE [dbo].[SalesPerson]
ADD CONSTRAINT [FK_SalesPerson_SalesManager]
    FOREIGN KEY ( [SalesManagerID] )
    REFERENCES [dbo].[SalesPerson] ( [SalesPersonID] )
GO

-- Using the Employees table in the Northwind database
INSERT INTO [dbo].[SalesPerson] 
( [SalesPersonID], [FirstName], [LastName], [SalesManagerID], [YTDSales] )
VALUES ( 1, 'Andrew', 'Fuller', NULL, 1000000.00 ),
       ( 2, 'Steven', 'Buchanan', 1, 600000.00),
       ( 3, 'Laura', 'Callahan', 1, 400000.00),
       ( 4, 'Janet', 'Leverling', 2, 250000.00),
       ( 5, 'Nancy', 'Davolio', 3, 350000.00),
       ( 6, 'Margaret', 'Peacock', 2, 150000.00),
       ( 7, 'Michael', 'Suyama', 3, 400000.00),
       ( 8, 'Robert', 'King', 2, 125000.00),
       ( 9, 'Anne', 'Dodsworth', 3, 275000.00)
GO

A report needs to be generated listing the sales person that earned the highest year-to-date sales figures for each top earning manager starting from the top manager. The final output required given the sample data above is as follows:

SalesPersonID  FirstName  LastName   YTDSales
-------------- ---------- ---------- -----------
             1 Andrew     Fuller     1000000
             2 Steven     Buchanan   600000
             4 Janet      Leverling  250000

Using a recursive common table expression (CTE) together with the TOP operator to get the top–earning sales person per manager, here’s what the query will look like:

WITH [TopSalesPersons]
AS (
    SELECT TOP 1 [SalesPersonID], [FirstName], [LastName], [YTDSales]
    FROM [dbo].[SalesPerson]
    WHERE [SalesManagerID] IS NULL
    ORDER BY [YTDSales] DESC
    UNION ALL
    SELECT TOP 1 B.[SalesPersonID], B.[FirstName],
                 B.[LastName], B.[YTDSales]
    FROM [TopSalesPersons] A INNER JOIN [dbo].[SalesPerson] B
      ON A.[SalesPersonID] = B.[SalesManagerID]
    ORDER BY B.[YTDSales] DESC
)
SELECT * FROM [TopSalesPersons]
GO

Unfortunately, because of the restriction with recursive common table expressions where the TOP operator cannot be used in the recursive part of the common table expression, the following error message is generated:

Msg 461, Level 16, State 1, Line 1
TOP operator is not allowed in the recursive part of a recursive common table expression 'TopSalesPersons'.

Solution / Work Around

To work around this restriction of the recursive common table expression, the query above can be rewritten with the following query which removes the use of the TOP operator and replaced with the ROW_NUMBER ranking function:

WITH [TopSalesPersons]
AS (
    SELECT TOP 1 [SalesPersonID], [FirstName], [LastName], [YTDSales], 
                 CAST(1 AS INT) AS [Rank]
    FROM [dbo].[SalesPerson]
    WHERE [SalesManagerID] IS NULL
    ORDER BY [YTDSales] DESC
    UNION ALL
    SELECT B.[SalesPersonID], B.[FirstName], B.[LastName], B.[YTDSales], 
           CAST(ROW_NUMBER() OVER(ORDER BY B.[YTDSales] DESC) AS INT) AS [Rank]
    FROM [TopSalesPersons] A INNER JOIN [dbo].[SalesPerson] B
      ON A.[SalesPersonID] = B.[SalesManagerID]
    WHERE A.[Rank] = 1
)
SELECT [SalesPersonID], [FirstName], [LastName], [YTDSales]
FROM [TopSalesPersons]
WHERE [Rank] = 1

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 in each partition. The ORDER BY clause, in this case the ORDER BY [YTDSales], determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Since the requirement only needs the top sales person per manager based on the year-to-date sales, only those sales person where their rank is equal to 1 is processed and returned by the query.

The result of this query using the sample data shown above is as follows:

SalesPersonID  FirstName  LastName   YTDSales
-------------- ---------- ---------- -----------
             1 Andrew     Fuller     1000000
             2 Steven     Buchanan   600000
             4 Janet      Leverling  250000