How to order by union result?

Question

I have two queries that are "merged" with Union:

A

union 

B

I want the result to be order by a specific column.

for example:

A

id   sum    dateissue
5     30     1.1.15
8     14     2.4.15

B

id   sum    dateissue
13     30     1.4.12
8     14     4.4.15

Desired result after Union with order by dateissue column:

id   sum    dateissue
13    30     1.4.12           : from B           
5     30     1.1.15           : from A
8     14     2.4.15           : from A
8     14     4.4.15           : from B

I tried to do

(A)
Union
(B)
order by dateissue

but it gives error:

ERROR: column "dateissue" does not exist

How can I sort the result of the union?

Any logic behind that particular order?Shoudn`t 1.1 be ahead of 1.4? — Mihai, Oct 18 '15 at 07:44
@Mihai it's ordered by the column issuedate.... look at the year... 1.4.12 comes before 1.1.15 — SMW, Oct 18 '15 at 07:46
Provide the code of the queries A and B. A usual `UNION` can have an `ORDER BY`. — ypercubeᵀᴹ, Oct 18 '15 at 07:50
What type of column it is I meant it it`s a string even with ORDER BY it wont work — Mihai, Oct 18 '15 at 07:58

Steve P. · Accepted Answer · 2015-10-18T08:02:53.053

2

You just need to make sure that the first select actually extracts 'dateissue,' ie

select id, sum, dateissue
from a
where...

union

select id, sum, dateissue
from a
where...

order by dateissue;

To clarify, the 'order by' is applied to the complete resultset (after the union).

edited Oct 18 '15 at 08:02

answered Oct 18 '15 at 07:48

Steve P.

14,489
8
42
72

score 1 · Answer 2 · edited May 23 '17 at 12:14

SELECT  * 
FROM 
        (
            SELECT id, sum, dateissue FROM A 
            UNION ALL
            SELECT id, sum, dateissue FROM B
        ) dum
ORDER BY dateissue

the order is affect in SELECT *

SELECT * FROM (

SELECT id, sum, dateissue FROM A 
UNION ALL
SELECT id, sum, dateissue FROM B
) dum ->

id   sum    dateissue
5     30     1.1.15
8     14     2.4.15
13     30     1.4.12
8     14     4.4.15

ORDER BY dateissue ->

id   sum    dateissue
13    30     1.4.12         
5     30     1.1.15
8     14     2.4.15 
8     14     4.4.15

you can use UNION ALL : What is the difference between UNION and UNION ALL? in case of same row

score 0 · Answer 3 · answered Oct 18 '15 at 07:47

0

Try this one:

SELECT * FROM A
UNION
SELECT * FROM B
ORDER BY dateissue;

answered Oct 18 '15 at 07:47

Md Mahfuzur Rahman

2,319
2
18
28

How to order by union result?

3 Answers3