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 (890771)

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

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

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 (890771)

SYMPTOMS

CAUSE

WORKAROUND

STATUS