You can use Ordinal position in the ORDER BY Clause for sorting the resultset. The ordinal position refers the column position in the SELECT statement. Consider the following select statement
select * from
(
select 17 as a,2 as b
union all
select 1 as a,25 as b
) t
order by 1
The above sorts the resultset based on the column number 1 (a) in the select statement Consider the following statement
select 1 as a,2 as b order by COUNT(*)
The count(*) will return 1 and the resultset should be sorted by first column. Let us replace count(*) with value
select 1 as a,2 as b order by 1
It works fine. Now when you specify 5 in the order by clause
select 1 as a,2 as b order by 5
It throws an error
Msg 108, Level 16, State 1, Line 1
The ORDER BY position number 5 is out of range of the number of items in the select list.
Ok. Execute this code and see the result
select count(*)+4
It returns 5 as count(*) is 1 Now replace 5 with count(*)+4 in the select statement and you would expect the same error. Right?
select 1 as a,2 as b order by count(*)+4
But note that the above does not return error message even though there is no 5th column in the select statement The following select statement throws an error
select @@SERVERNAME+8092834
But when the above is used in an order by clause it does not throw an error
select 1 as a, 2 as b order by @@SERVERNAME+8092834
You can also use a subquery in the ORDER BY Clause as below
select 1 as a, 2 as b order by (select 'test')
You can use multiple queries too
select 1 as a, 2 as b order by (select 'test'),(select 7823)
This query works fine
select 1 as a, 2 as b order by @@SERVERNAME+8092834
But when there are more than a row, the following throws an error
select * from (select 1 as a, 2 as b union select 5,76) as t order by @@SERVERNAME+8092834
All the below queries will work fine
select a,b from
(
select 1 as a,2 as b
union all
select 1 as a,2 as b
) t
group by a,b order by SUM(1)+'a'
select d from
(
select 2 as d union all select 1
) t
order by (select 100) /45+@@CONNECTIONS-90
select d from
(
select 2 as d union all select 1
) t
order by (select 100),(select 'test'),(select max(name) from sysobjects)
select d from
(
select 2 as d union all select 1
) t
order by (select max(n) from (select 'test' as n union all select '') t) asc
select 1 as a,2 as b order by @@SERVERNAME * count(*)
select 1 as a,2 as b order by @@SERVERNAME * count(*)*'test'
select 1 as a,2 as b order by @@SERVERNAME * count(*)*'test'/56.324
So from these examples it is clear that SQL Server in some cases omits the expression using system functions completely and ignores the errors too. So you need to beware of this behaviour