A workaround for ORDER BY clause in Subqueries

A workaround for ORDER BY clause in Subqueries

We cannot use ORDER BY clause in subqueries.
Consider the following subquery for example :


select * into tempaaa from (
select routeid , stopno , stopname , timetoreach , droptime
from rsc_schule_pickupmaster
order by routeid , stopno ) A
When we execute this query , we will get the following  SQL error :

Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

But sometimes we require the data to be orderd.
A quick workaround for this is to use the TOP X clause with X = count of records returned by subquery.
For example, suppose the above subquery returns 73 recs.
We can safely use the following query to populate table tempaaa :


select * into tempaaa from (
select TOP 73 routeid , stopno , stopname , timetoreach , droptime
from rsc_schule_pickupmaster
order by routeid , stopno ) A