ORDER BY in UNION

[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.