Skip to content

column-aliases-in-order-by-clause-cannot-be-prefixed-by-table-alias.md

Latest commit

 

History

History
71 lines (56 loc) · 2.99 KB

column-aliases-in-order-by-clause-cannot-be-prefixed-by-table-alias.md

File metadata and controls

71 lines (56 loc) · 2.99 KB
title Column aliases in ORDER BY clause cannot be prefixed by table alias | Microsoft Docs
ms.custom
ms.date 03/06/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology database-engine
ms.topic conceptual
helpviewer_keywords
aliases [SQL Server], columns
ms.assetid fee7328f-6e8d-4005-930b-56fb6f17e0b2
author mashamsft
ms.author mathoma
manager craigg

Column aliases in ORDER BY clause cannot be prefixed by table alias

In [!INCLUDEssVersion2005] or later, column aliases in the ORDER BY clause cannot be prefixed by the table alias.

Component

[!INCLUDEssDE]

Description

For example, the following query executes in [!INCLUDEssVersion2000], but returns an error in [!INCLUDEssKatmai]:

USE AdventureWorks2012;  
GO  
SELECT FirstName AS f, LastName AS l  
FROM Person.Contact p  
ORDER BY p.l

The [!INCLUDEssDEversion10] does not match p.l in the ORDER BY clause to a valid column in the table.

Exception

If the prefixed column alias that is specified in the ORDER BY clause is a valid column name in the specified table, the query executes without error; in [!INCLUDEssCurrent], the semantics of the statement might be different. For example, the column alias (id) specified in the following statement is a valid column name in the sysobjects table. In [!INCLUDEssVersion2000], when the statement executes, the CAST operation is performed after the result set is sorted. This means the name column is used in the sort operation. In [!INCLUDEssKatmai], the CAST operation occurs before the sort operation. This means the id column in the table is used in the sort operation and returns the result set in an unexpected order.

SELECT CAST (o.name AS char(128)) AS id  
FROM sysobjects AS o  
ORDER BY o.id;

Corrective Action

Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause in either of the following ways:

  • Do not prefix the column alias in the ORDER BY clause, if possible.

  • Replace the column alias with the column name.

For example, both of the following queries execute without error in [!INCLUDEssKatmai]:

USE AdventureWorks2012;  
GO  
SELECT FirstName AS f, LastName AS l  
FROM Person.Contact p  
ORDER BY l  
  
USE AdventureWorks2012;  
GO  
SELECT FirstName AS f, LastName AS l  
FROM Person.Contact p  
ORDER BY p.LastName

See Also

Database Engine Upgrade Issues
SQL Server 2014 Upgrade Advisor [new]