Sunday, November 28, 2010

Subquery columns are available from outside of a subquery

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
) a
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.

No comments:

Post a Comment