Subquery columns are available from outside of a subquery
e.g. the following perfectly works :
select Routename from (
select distinct RouteName , RouteId from Sec45.dbo.SCHULE_ROUTEMASTER
order by a.RouteId
Eventhough you will notice that the outer query has only one column, Routename,
we can have an ORDER BY cLAUSE in the outer query, which refers to a column
within a subquery,
NOTE that we cannot put the ORDER BY in inner subquery, since it is invalid to
put ORDER BY in a subquery.
We cannot have something like this :
select distinct Routename from Sec45.dbo.SCHULE_ROUTEMASTER
order by Routeid
This is because RouteId is not contained in
aggregates list, hence we cannot order by it.