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 - View products that this article applies to.
Expand all | Collapse all

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
Back to the top | Give Feedback

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.
Back to the top | Give Feedback

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
Back to the top | Give Feedback

STATUS

This behavior is by design.
Back to the top | Give Feedback

Properties

Article ID: 890771 - Last Review: November 2, 2007 - Revision: 1.5
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
Keywords: 
kbquery kbinfo kbtshoot kbprb KB890771
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top