I have came accross the same question at Microsoft TechNet Forum and here is an answer to that;
The reason which i know why Duplicate Column Names are not allowed in ORDER BY Cluase is pretty simple;
Please see the below SQL what you think it might generate as output, if SQL allows this kind of syntax it will obviously very hard for optimizer to sort which thing to do. To avoid such cases MS SQL doesn't allow Duplicate Column Names.
All Columns appeared in the ORDER BY Cluase must be unique. Similarly you can't referrence a Column Name by its position in the SELECT list if its Column Name is appearing in the ORDER BY Clause.
Both SQL statement which i have mentioned below are incorrect in syntax.
select id from #t
order by id,id desc
select names ,id from #t
order by 2,id
Please let me know if this doesn’t work for you. Hope I have answered you correctly. Also i have put these details on MSDN Library so others users can benefit from it; Take a look at below;
commented on Oct 23 2011 2:46AM