FIX: SQL Server Ignores ORDER BY When You Use ROLLUP and Only One Row Meets the Search Criteria (308803)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q308803
BUG #: 355092 (SHILOH_BUGS)

SYMPTOMS

SQL Server ignores the ORDER BY clause when you use a ROLLUP operator and only one record satisfies the query predicates.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

You can use this code to reproduce the problem: 
USE pubs
GO

CREATE TABLE t ( x varchar(5) )

INSERT INTO t VALUES ( 'A' )
INSERT INTO t VALUES ( 'B' )
GO

SELECT GROUPING(x) AS g0,x
FROM t
WHERE (x = 'A')
GROUP BY x WITH ROLLUP 
ORDER BY GROUPING(x) DESC
GO

--This query returns the results in ascending order instead of descending order.
The result of the query is: 
g0   x     
---- ----- 
0    A
1    NULL

(2 row(s) affected)
The query produces the correct result if multiple rows return. For example: 
SELECT GROUPING(x) AS g0, x
FROM t
WHERE (x = 'A') OR (x = 'B')
GROUP BY x WITH ROLLUP 
ORDER BY GROUPING(x) DESC
The query result is: 
g0   x     
---- ----- 
1    NULL
0    A
0    B

(3 row(s) affected)
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbbug kbfix KB308803
©2003 Microsoft Corporation. All rights reserved.