Order By clause not so useful

If you have ever come across this error in SQL

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.

then all SQL is trying to tell you is that you cannot use your ORDER BY clause within a subquery.

So if you have something like this, it sure as hell won’t work:

SELECT x, y, z

UNION

SELECT * FROM(

SELECT DISTINCT x, y, z

FROM (

SELECT x, y, z

FROM a

WHERE

UNION

SELECT x, y, z

FROM b

WHERE

)

ORDER BY z

)

Work arounds? A little hunting around showed that you could “use” a ORDER BY in a subquery if you used the TOP X PERCENT in your SELECT statement which would go something like this:

SELECT x, y, z

UNION

SELECT * FROM(

SELECT TOP 100 PERCENT x, y, z

FROM (

SELECT x, y, z

FROM a

WHERE

UNION

SELECT x, y, z

FROM b

WHERE

)

ORDER BY z

)

Why I stressed on “use” there is because although you can use it, it doesn’t make life any easier. Basically it allows the usage of ORDER BY, but it never does ORDER BY anything. You get back an unordered result set.

The workaround I used was adding an extra column that had blank data for the row I wanted sorted on the top and the actual data I wanted sorted by for the rest of the rows; then sorted it based on this extra column. Crude, but it works.

I thought this would be an easy thing to do, but it doesn’t seem so.