Pinal Dave in his blog posted this about using a column name and column number in the Order by clause. He made some valid points on why the second method should not be used. However, there is a case in which second method can be useful

I had already blogged about Dynamic PIVOT in which you can pass the queries, aggerate columns, etc. What if you want to order the resultset by the first column always?. You can use column number in the order by clause. So modify the procedure provided in the above post as follows

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100), 
@Summaries varchar(100)
) as 
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
 

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
    select * from 
    (
        '+@select+'
    ) as t 
    pivot 
    (
        '+@Summaries+' for pivot_col in ('+@pivot+')
    ) as p
ORDER BY 1
' 
exec(@sql) 

So my adivise is use it in a dynamic sql where column names are unknown