MS SQL 2005 - order by collate with subquery fails

Question

This is a simplified version of a real query to show the problem. It can be executed on the sample Adventureworks database:

SELECT Person.Address.*,
 (SELECT TOP 1 [Name]
 FROM Person.StateProvince
 WHERE Person.StateProvince.StateProvinceId = Person.Address.StateProvinceId AND Person.StateProvince.TerritoryId IN (5, 6, 7)
 ORDER BY Person.StateProvince.TerritoryId) AS [Name]
FROM Person.Address
ORDER BY [Name]

This works well, but my problem is that if I add collation to the order by field, I get a strange error message:

SELECT Person.Address.*,
 (SELECT TOP 1 [Name]
 FROM Person.StateProvince
 WHERE Person.StateProvince.StateProvinceId = Person.Address.StateProvinceId AND Person.StateProvince.TerritoryId IN (5, 6, 7)
 ORDER BY Person.StateProvince.TerritoryId) AS [Name]
FROM Person.Address
ORDER BY [Name] COLLATE Chinese_PRC_CI_AI

And the error is:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Name'.

The query with the collate clause works well if there's no subquery, but I need it to be like this (for a complicated reason, but please believe me :)).

Does anybody know why this query fails, and what can be a possible workaround? Is this a bug?

Thanks! Adam

Interesting, I don't have AdventureWorks at my disposal right now, but I just tried a similar query on a different database but couldn't replicate your problem. — pavanred, Nov 03 '10 at 12:19
Hi! You can download the AdventureWorks db from http://msftdbprodsamples.codeplex.com/releases/view/4004 — Ádám Katona, Nov 03 '10 at 12:26

score 2 · Accepted Answer · answered Nov 03 '10 at 12:40

SQL Server does not allow you to refer to columns by alias in the order by clause. For example, this won't work:

select id+1 as x from MyTable order by x

But you can fix that by repeating the column definition:

select id+1 as x from MyTable order by id+1

Or by using a subquery:

select * from (select id+1 as x from MyTable) as subquery order by x

In your case, the best fix is probably to use a subquery:

SELECT  *
FROM    (
        SELECT  Person.Address.*
        ,       (
                SELECT  TOP 1 [Name]
                FROM    Person.StateProvince
                WHERE   Person.StateProvince.StateProvinceId = 
                            Person.Address.StateProvinceId 
                        AND Person.StateProvince.TerritoryId IN (5, 6, 7)
                ORDER BY 
                        Person.StateProvince.TerritoryId
                ) AS [Name]
        FROM    Person.Address
        ) as SubqueryAlias
ORDER BY 
        [Name]

Thanks for your quick answer, now I understand why this is (or isn't) working. Another great explanation here: http://social.technet.microsoft.com/Forums/en/transactsql/thread/9d948214-9bae-4df0-870d-507e43407af9 — Ádám Katona, Nov 05 '10 at 06:40

MS SQL 2005 - order by collate with subquery fails

1 Answers1