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'
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 )
id | val |
---|---|
1 | 500 |
2 | 800 |
3 | 700 |
4 | 100 |
5 | NULL |
9 | 700 |
id | val |
---|---|
9 | 700 |
4 | 100 |
1 | 500 |
3 | 700 |
2 | 800 |
5 | NULL |
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