ORDER BY in UNION

_Flaviu

Is there possible to get an UNION in such a way that second part of UNION to be ordered ? I have:

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

The select from table1 will always get one row, and I need to order just records that come from table2, which could be more than one row ... it is possible to achieve that by SQL ? P.S. I am using SQL Server.

jschell

_Flaviu wrote:

it is possible to achieve that by SQL ?

No. There are however solutions using derived languages such as TSQL or PL/SQL. You would need to specify which database you are using however for any consideration of that.

_Flaviu

SQL Server (from Microsoft)

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.