Microsoft KB Archive/890771

= You receive an error message when you perform a query that contains an alias that has the same name as two or more columns in the ORDER BY clause =

Article ID: 890771

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition

-





SYMPTOMS
When you try to perform a query in Microsoft SQL Server 2000, you receive the following error message:

Server: Msg 169, Level 15, State 3, Line 8

A column has been specified more than once in the order by list. Columns in the order by list must be unique.

This problem occurs when the following conditions are true:
 * The query contains two or more columns that have the same name in the ORDER BY clause.
 * The query contains an alias in the SELECT clause that has the same name as the two or more columns in the ORDER BY clause.

For example, when you run the following Transact-SQL script, you receive this error message: USE Northwind GO SELECT TOP 1 customers.CustomerID as customerid, customers.CompanyName as companyname FROM customers INNER JOIN orders on customers.CustomerID = orders.CustomerID ORDER BY customers.CustomerID ASC, orders.CustomerID DESC GO



CAUSE
This problem occurs because the ORDER BY clause list must be unique. When an alias that has the same name in the SELECT clause is specified, the ORDER BY clause list is no longer unique.



WORKAROUND
To work around this problem, you must make sure that the alias that you specified in the SELECT clause is unique if you are going to include the column in the ORDER BY clause list. For example, when you run the following Transact-SQL script, no error message is returned: USE Northwind GO SELECT TOP 1 customers.CustomerID as customerid1, customers.CompanyName as companyname FROM customers INNER JOIN orders on customers.CustomerID = orders.CustomerID ORDER BY customers.CustomerID ASC, orders.CustomerID DESC GO



STATUS
This behavior is by design.

Keywords: kbquery kbinfo kbtshoot kbprb KB890771

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.