The Parallel Data Warehouse (PDW) features are not enabled.
This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY
clause with aggregate functions like MIN
:
Windowing function support was considerably extended in 2012, compared with the basic implementation available starting with SQL Server 2005. The extensions were made available in Parallel Data Warehouse before being incorporated in the box product. Because the various editions share a common code-base, misleading error messages like this are possible.
If you are interested, the call stack when the aggregate is verified by the parser is shown below. Because the aggregate has an OVER
clause with ORDER BY
, a check for PDW is issued:
This check immediately fails with a parser error:
Luckily, you do not need an windowed aggregate that supports ORDER BY
framing to solve your code problem.
This construction is not currently supported in SQL Server. It could (and should, in my opinion) be implemented in a future version.
Applying one of the workarounds listed in the feedback item reporting this deficiency, your query could be rewritten as:
WITH UpdateSet AS
(
SELECT
AgentID,
RuleID,
Received,
Calc = SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (
PARTITION BY AgentID, RuleID)
FROM
(
SELECT
AgentID,
RuleID,
Received,
rn = ROW_NUMBER() OVER (
PARTITION BY AgentID, RuleID, GroupID
ORDER BY GroupID)
FROM #TempTable
WHERE Passed = 1
) AS X
)
UPDATE UpdateSet
SET Received = Calc;
The resulting execution plan is:
This has the advantage of avoiding an Eager Table Spool for Halloween Protection (due to the self-join), but it introduces a sort (for the window) and an often-inefficient Lazy Table Spool construction to calculate and apply the SUM OVER (PARTITION BY)
result to all rows in the window. How it performs in practice is an exercise only you can perform.
The overall approach is a difficult one to make perform well. Applying updates (especially ones based on a self-join) recursively to a large structure may be good for debugging but it is a recipe for poor performance. Repeated large scans, memory spills, and Halloween issues are just some of the issues. Indexing and (more) temporary tables can help, but very careful analysis is needed especially if the index is updated by other statements in the process (maintaining indexes affects query plan choices and adds I/O).
Ultimately, solving the underlying problem would make for interesting consultancy work, but it is too much for this site. I hope this answer addresses the surface question though.
Alternative interpretation of the original query (results in updating more rows):
WITH UpdateSet AS
(
SELECT
AgentID,
RuleID,
Received,
Calc = SUM(CASE WHEN Passed = 1 AND rn = 1 THEN 1 ELSE 0 END) OVER (
PARTITION BY AgentID, RuleID)
FROM
(
SELECT
AgentID,
RuleID,
Received,
Passed,
rn = ROW_NUMBER() OVER (
PARTITION BY AgentID, RuleID, Passed, GroupID
ORDER BY GroupID)
FROM #TempTable
) AS X
)
UPDATE UpdateSet
SET Received = Calc
WHERE Calc > 0;
Note: eliminating the sort (e.g. by providing an index) might reintroduce the need for an Eager Spool or something else to provide the necessary Halloween Protection. Sort is a blocking operator, so it provides full phase separation.
Best Answer
The
Rnk
is a column computed in theSELECT
clause. It's not avaiable in theWHERE
clause of the same level, as the logical order of execution a query isFROM -> WHERE -> SELECT
.You have to wrap the query in a subquery. You can use either a CTE (Common Table Expression):
or a derived table:
As a side note for future readers - worth reading - What's the difference between a CTE and a Temp Table? by JNK♦