Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 584107
Opened: 8/6/2010 4:31:06 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

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:

http://msdn.microsoft.com/en-us/library/ms143179%28SQL.100%29.aspx

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
Details
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
Hi,
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
Sign in to post a workaround.