ORDER BY in UNION

[Richard Deeming]

The ORDER BY clause applies to the entire results; you can't make it only apply to one part of a UNION. However, you could add an additional column to indicate which part of the UNION the row belongs to, and add that to your ORDER BY statement:

SELECT column_name(s), 0 As QueryPart FROM table1

UNION ALL

SELECT column_name(s), 1 As QueryPart FROM table2

ORDER BY QueryPart, SomeOtherColumn

If you don't want the additional column to be included in your results, you can use a subquery or CTE to hide it:

WITH cteUnion As
(
SELECT column_names(s), 0 As QueryPart FROM table1

UNION ALL

SELECT column\_name(s), 1 As QueryPart FROM table2

)
SELECT
column_name(s)
FROM
cteUnion
ORDER BY
QueryPart,
SomeOtherColumn
;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Richard Andrew x64]

How about:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM
(SELECT column_name(s) FROM table2 ORDER BY 3)

?

The difficult we do right away... ...the impossible takes slightly longer.

[David Mujica]

I know this is not going to be a popular opinion, but ... 1) Create a #TEMP table 2) Insert into the table with part 1 of the query 3) Insert into the table with part 2 (sorted) Return the dataset from the #TEMP table

[jschell]

That is probably how I would do it. Although temp tables are also database specific the general idea and the layout of the solution works across different types of databases.

[k5054]

Is there any guarantee that the temp table will be returned in the insertion order? If not, you'd have to add an identity column and return the temp table sorted on that.

Keep Calm and Carry On

[jschell]

k5054 wrote:

will be returned in the insertion order?

Unlikely. And would not work anyways. Because you would do first one query then the second. So of course the temp table would need to provide a way to order it.

[_Flaviu]

I have tried:

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
SELECT b.id, b.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416)

But it doesn't like it:

Executing SQL directly; no cursor.
Incorrect syntax near ')'.
Statement(s) could not be prepared.

Did I understand correctly your thought?

[_Flaviu]

I have tried:

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT b.id, b.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416)

But it doesn't like it:

SQL
Executing SQL directly; no cursor.
Incorrect syntax near ')'.
Statement(s) could not be prepared.

Did I understand correctly your thought?

[Richard Andrew x64]

I'm sorry, try this slight modification:

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S

The difficult we do right away... ...the impossible takes slightly longer.

[_Flaviu]

Yes, that's worked:

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S

but soon as I put ORDER BY:

SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416 ORDER BY 2) AS S

Error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

[Richard Andrew x64]

Whoops! I guess that's why nobody else recommended that. I forgot that ORDER BY is not allowed in derived tables. I think the temporary table is probably your best option.

The difficult we do right away... ...the impossible takes slightly longer.