I have the following virtual column generated from an aggregate over a sorted partition,
MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )
However, when I execute that, I get the following.
Msg 11305, Level 15, State 10, Line 12
The Parallel Data Warehouse (PDW) features are not enabled.
This is where it gets interesting though, without a sort order on the partition, it works:
MIN(picture_id) OVER ( PARTITION BY [360_set] )
And, further, ROW_NUMBER()
a window function (not an aggregate function) works with an explicit order on the partition.
ROW_NUMBER() OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )
How come the desired statement doesn't work? Where is this documented? The version information was requested, this is what I in Help → About.
Microsoft SQL Server Management Studio 10.0.5512.0
Microsoft Analysis Services Client Tools 10.0.5500.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.10.9200.16635
Microsoft .NET Framework 2.0.50727.5472
Operating System 6.1.7601
The result from SELECT @@VERSION
is Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
Best Answer
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 likeMIN
: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 withORDER 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.