SELECT TOP N with UNION and ORDER BY

A Union query works thus: execute the queries, then apply the order by clause. So with

SELECT TOP 5 [ID], [Description], [Inactive]FROM #T1UNION ALLSELECT TOP 5 [ID], [Description], [Inactive]FROM #T2ORDER BY [Inactive], [Description]; 

you select five arbitrarily chosen records from #T1 plus five arbitrarily chosen records from #T2 and then you order these. So you need subqueries or with clauses. E.g.:

SELECT * FROM(  (    SELECT TOP 5 [ID], [Description], [Inactive]    FROM #T1    ORDER BY [Inactive], [Description]  )  UNION ALL  (    SELECT TOP 5 [ID], [Description], [Inactive]    FROM #T2    ORDER BY [Inactive], [Description]  )) t;

So your workaround is not a workaround at all, but the proper query.

You should move the whole UNION ALL within a subquery:

SELECT *FROM (SELECT TOP 5 [ID], [Description], [Inactive]       FROM #T1       ORDER BY [Inactive], [Description]      UNION ALL      SELECT TOP 5 [ID], [Description], [Inactive]       FROM #T2       ORDER BY [Inactive], [Description]) T3ORDER BY [Inactive], [Description];GO

ID | Description | Inactive-: | :---------- | :------- 4 | Four        | False    6 | Six         | False   10 | Ten         | False    2 | Two         | False    8 | Eight       | True    

There is a major difference between query #2 and #3 - the ORDER BY clause on #T1.

The thumb rule is -in a SQL query if you don't specify the ORDER BY cause, "TOP" will just return random set of records. After you get these "random" rows, the sorting comes into play. And hence there is no "proper" sorting taking place.