Microsoft KB Archive/246201: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - """ to """) |
||
Line 57: | Line 57: | ||
order by au_lname | order by au_lname | ||
</pre> | </pre> | ||
The three results rows should be ordered | The three results rows should be ordered "Hunter", "Karsen", "Locksley" but the results are reversed. | ||
</div> | </div> | ||
Line 64: | Line 64: | ||
== WORKAROUND == | == WORKAROUND == | ||
Instead of using | Instead of using "*" to return all columns in the table, specify the column names explicitly. The following query works as expected: | ||
<pre class="codesample">use pubs | <pre class="codesample">use pubs | ||
select au_id, au_lname, au_fname, phone, address, city, state, zip, contract | select au_id, au_lname, au_fname, phone, address, city, state, zip, contract |
Latest revision as of 13:49, 21 July 2020
Article ID: 246201
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q246201
BUG #: 56519 (SQLBUG_70)
SYMPTOMS
The ORDER BY clause of a SELECT statement may be ignored when the sub-select statement also contains an ORDER BY clause. For example, the following query returns results in the wrong order:
use pubs select * FROM (select top 3 * from authors where au_lname < 'MacFeather' order by au_lname desc) as a order by au_lname
The three results rows should be ordered "Hunter", "Karsen", "Locksley" but the results are reversed.
WORKAROUND
Instead of using "*" to return all columns in the table, specify the column names explicitly. The following query works as expected:
use pubs select au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM (select top 3 * from authors where au_lname < 'MacFeather' order by au_lname desc) as a order by au_lname
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
Keywords: kbbug kbfix KB246201