Subquery in group by works in SQL 2005, does not work in SQL 2008 - by Scott3721

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 584107 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 8/6/2010 4:31:06 PM
Access Restriction Public


In SQL 2005 you could have a group by statement in a subquery without a problem, in 2008 SP1 this does not work. This is not documented on the breaking changes page for SQL Server 2008:

It does not work even when running databases set to SQL 2005 compatibility level.

While this is not very useful and it is easy to work around, it can show up when migrating from SQL 2005 to SQL 2008. This should work when running databases in SQL 2005 compatibility and it should be documented on the breaking changes page
Sign in to post a comment.
Posted by Microsoft on 10/22/2010 at 11:40 AM
Hi Scott,
We have now documented the breaking change in SQL Server 2008 R2 Books Online. The content for SQL Server 2008 is locked down so it will not reflect the issue. Thanks for reporting the issue.

Umachandar, SQL Programmability Team
Posted by Microsoft on 8/27/2010 at 12:25 PM
Thanks for reporting the issue. We will take a look at why the old syntax doesn't work in 90 compat level in SQL Server 2008.
FYI - ANSI SQL disallows even expressions as GROUP BY items. The change to disallow sub-queries was done to align more with the standards. I will also track the Books Online documentation for this behavior change.

Umachandar, SQL Programmability Team