Sort on T-SQL unioned query with CTE

Question

I need to do a sort on a T-SQL unioned query. No matter how I write the query, I'm getting a syntax error once I introduce the ORDER BY clause.

Incorrect syntax near the keyword 'order'

T-SQL query

WITH s1
AS (select '1' as id
    union
    select '4' as id
    union
    select '2' as id
    union
    select '3' as id
    union
    select '5' as id
   ),
     s2
AS (select '4' as id,
           100 as val
    union
    select '1' as id,
           500 as val
    union
    select '3' as id,
           700 as val
    union
    select '2' as id,
           800 as val
   )
(select '9' as id,
        700 as val) -- id 9 should stay on first row
UNION
(select s1.id,
        s2.val
 from s1
     Left join s2
         on s1.id = s2.id  --Order by s2.val causing a syntax error ) 

Query output

id	val
1	500
2	800
3	700
4	100
5	NULL
9	700

Desired output

id	val
9	700
4	100
1	500
3	700
2	800
5	NULL

Answer 1

Add an additional column specially for ordered, but don't select it e.g.

SELECT id, val
FROM (
  SELECT '9' AS id, 700 AS val, 1 AS SortOrder
  UNION ALL -- Always use union all where you can for performance
  SELECT s1.id, s2.val, 0 AS SortOrder
  FROM s1
  LEFT JOIN s2 ON s1.id = s2.id
) x
ORDER BY SortOrder DESC, val