CODEWITHSUNDEEP
sqlpostgresql
SMW
SMW

Reputation: 339

How to order by union result?

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?

Upvotes: 0

Views: 103

Answers (3)

SilentT
SilentT

Reputation: 135

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

Upvotes: 1

Steve P.
Steve P.

Reputation: 14699

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

Upvotes: 2

Md Mahfuzur Rahman
Md Mahfuzur Rahman

Reputation: 2359

Try this one:

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

Upvotes: 0

Related Questions