SQL order with Union

Question

I want to select the union of the results from two queries. What I'm trying to do is select one entry with a certain constraint, then add the remaining order by some other constraint.

I tried to do Union, but the order by on the second query fails, as it cannot order on a sub-query.

So what I did is declare a table variable, and then insert the single entry, then insert the rest of the entries order by constraint.

Is there a way to do this using union?

Can you post the queries? – Radu Gheorghiu Oct 23 '13 at 19:01 — Radu Gheorghiu, Oct 23 '13 at 19:01
What kind of database are you using? – nvuono Oct 23 '13 at 19:01 — nvuono, Oct 23 '13 at 19:01
I am using SQL Server, but I found the solution thanks! – emochoco Oct 23 '13 at 19:16 — emochoco, Oct 23 '13 at 19:16

score 0 · Accepted Answer · answered Oct 23 '13 at 19:03

You could just wrap up the results of the queries that are unioned together and then perform the sort. Something like:

SELECT MainQ.* FROM 
(
  (SELECT * FROM MyTable AS T1 WHERE MyField = 1)
  UNION 
  (SELECT * FROM MyTable AS T2 WHERE MyField = 2)
) AS MainQ
ORDER BY MainQ.TableID

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

Use the union statement as a subquery then select from it.

Select * from
(select fields,1 as orderby from table
 union all 
  select fields,2 from as orderby table)a
where whatever
order by orderby,whatever

The order by clause can also be heavily customized... How to define a custom ORDER BY order in mySQL

for mysql for example.

edited May 23 '17 at 10:25

Community

1
1

answered Oct 23 '13 at 19:04

Twelfth

7,070
3
26
34

SQL order with Union

2 Answers2