SQL Server : alias is ignored in Order by

Question

We have two tables in our application that both have a ShowOrder column.We are using NHibernate in our application and using HQL we join these two tables ordered by ShowOrder of first table and second table respectively. Here's a simplified version of my query :

SELECT pr.Id as Id,pr.Title as Title, pr.ShowOrder as ShowOrder 
FROM Process pr 
    INNER JOIN ProcessGroup prg ON pr.GroupId=prg.Id 
ORDER BY prg.ShowOrder,pr.ShowOrder

In general, our application is working without any problem.But we have an old application and a conversion routine to convert its database to our new application database.

Whenever we convert an old database to our new database An error will occur when the SQL server wants to execute the above query. The exception says: "A column has been specified more than once in the order by list"!!! If we select pr.ShowOrder without an alias everything is OK .It seems that if there's a ShowOrder alias in select list ,SQL server ignores table aliases and assumes that pr.ShowOrder and prg.ShowOrder are the same.

Answer 1

This is a change in behaviour between SQL Server 2000 and SQL Server 2005

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/198b29db-f223-4aaf-a9f2-965c8a1fa8eb

Answer 2

This is standard behavior of ANSI SQL and was adopted by SQL Server starting with the 2005 version.

ORDER BY does NOT operate on the column values from the Source tables (FROM clause), rather it technically ONLY operates on the output column values in the SELECT clause (**). So when you say "ShowOrder" in the ORDER BY clause, it is actually using the ShowOrder value in output list (which happens to be pr.ShowOrder). If you want to use both ShowOrders, you should put them both in the SELECT clause with different names.

The reason that is does not bark when you use the source table alias names (which technically it should), is for compatibility with SQL Server 2000, but that's a dangerous compatibility. No matter what alias name you use in the ORDER BY, it's actually only using the one that appears in the output list.

(** - and yes, there are ways around it, but they're there mostly to provide some compatiability with prior versions. You're better off just going with the standard).