ORACLE SQL How to implement a UNION clause but maintain the order of each SELECT

Question

Using a UNION clause in two SELECT statements how can I guarantee the order of execution? For example:

SELECT a.poclcdde, a.poclnpol, a.poclcdcl
  FROM dtpocl a
 WHERE     a.poclcdcl IN (216450,
                          562223,
                          250056,
                          202153,
                          8078)
       AND POCLCDCE = 0
       AND POCLTPSG = '01'
UNION
SELECT a.poclcdde, a.poclnpol, a.poclcdcl
  FROM dtpocl a
 WHERE     a.poclcdcl IN (216450,
                          562223,
                          250056,
                          202153,
                          8078)
       AND POCLTPSG = '02'

 POCLCDDE   POCLNPOL   POCLCDCL
---------- ---------- ----------
       100    1000001     202153
       100    5001021     216450
       100    9000386       8078
       100    9900633     250056
       100    9900634     250056
       100    9901720     562223
       100    9901763     562223
       200    1000001     202153
       200    5001021     216450

In this case, How can I guarantee that the first records are from query1 and the rest are from query2. I could use poclcdcl column (or a virtual column) and then order by that, but in this case I need to get DISTINCT rows.

  SELECT *
    FROM (SELECT a.poclcdde,
                 a.poclnpol,
                 a.poclcdcl,
                 1 AS TYPE
            FROM dtpocl a
           WHERE     a.poclcdcl IN (216450,
                                    562223,
                                    250056,
                                    202153,
                                    8078)
                 AND POCLCDCE = 0
                 AND POCLTPSG = '01'
          UNION
          SELECT a.poclcdde,
                 a.poclnpol,
                 a.poclcdcl,
                 2 AS TYPE
            FROM dtpocl a
           WHERE     a.poclcdcl IN (216450,
                                    562223,
                                    250056,
                                    202153,
                                    8078)
                 AND POCLTPSG = '02')
ORDER BY TYPE


  POCLCDDE   POCLNPOL   POCLCDCL       TYPE
---------- ---------- ---------- ----------
       200    1000001     202153          1
       100    1000001     202153          1
       100    9000386       8078          1
       100    9900634     250056          2
       100    9901720     562223          2
       100    9901763     562223          2
       200    5001021     216450          2
       100    9000386       8078          2
       100    5001021     216450          2
       100    9900633     250056          2

I need this to interact each row by that order: the first query prevails. Thanks

You can do UNION ALL. Add `1` to first select, and `2` to second. Order by that column. — jarlh, Aug 08 '16 at 13:37
`UNION ALL` don't remove duplicates. That is my second approach using `UNION` — milheiros, Aug 08 '16 at 13:39

score 3 · Answer 1 · answered Aug 08 '16 at 13:39

You could do this:

SELECT *
  FROM (SELECT a.poclcdde,
               a.poclnpol,
               a.poclcdcl,
               1 AS TYPE
          FROM dtpocl a
         WHERE     a.poclcdcl IN (216450,
                                  562223,
                                  250056,
                                  202153,
                                  8078)
               AND POCLCDCE = 0
               AND POCLTPSG = '01'
        UNION
        SELECT a.poclcdde,
               a.poclnpol,
               a.poclcdcl,
               2 AS TYPE
          FROM dtpocl a
         WHERE     a.poclcdcl IN (216450,
                                  562223,
                                  250056,
                                  202153,
                                  8078)
               AND POCLTPSG = '02'
               AND NOT EXISTS (SELECT NULL
                                 FROM dtpocl a
                                WHERE     a.poclcdcl IN (216450,
                                                         562223,
                                                         250056,
                                                         202153,
                                                         8078)
                                  AND POCLCDCE = 0
                                  AND POCLTPSG = '01')
               )
ORDER BY TYPE

Thanks @Tony. It works and it was one of the other solution that I've remember. But can `UNION` guaranteed to preserve the order? As this - http://stackoverflow.com/questions/15766359/does-union-all-guarantee-the-order-of-the-result-set - , I don't think so. — milheiros, Aug 08 '16 at 16:44
No, you need to specify whatever order you need in the ORDER BY clause. That goes for any query whatsoever! — Tony Andrews, Aug 08 '16 at 16:46

Thorsten Kettner · Answer 2 · 2016-08-08T19:46:50.493

You are selecting the same columns from the same table, so you don't need two queries, but just one with an appropriate WHERE clause. The ORDER BY clause is simple in your case; in more complicated cases you'd use CASE WHEN expressions in ORDER BY.

select poclcdde, poclnpol, poclcdcl
from dtpocl
where poclcdcl in (216450, 562223, 250056, 202153, 8078)
and
(
  (pocltpsg = '01' and poclcdce = 0)
  or
   pocltpsg = '02'
)
order by pocltpsg;

UPDATE: You say that you get duplicates, but once you use DISTINCT, you are not allowed to sort by pocltpsg. This is true, as for one combination of poclcdde, poclnpol, poclcdcl you may have records with both pocltpsg = '01' and '02'. So you would have to aggregate by poclcdde, poclnpol, poclcdcl and decide whether to sort by min(pocltpsg) or max(pocltpsg) (or any other aggregate for that matter).

select poclcdde, poclnpol, poclcdcl
from dtpocl
where poclcdcl in (216450, 562223, 250056, 202153, 8078)
and
(
  (pocltpsg = '01' and poclcdce = 0)
  or
   pocltpsg = '02'
)
group by poclcdde, poclnpol, poclcdcl
order by min(pocltpsg);

This solution don't remove duplicates. Put the `DISTINCT`, avoid me to use the `ORDER BY´ — milheiros, Aug 08 '16 at 16:34
Actually, @Thorsten is right. Because your 2 queries filter on `pcltpsg` but expect different values, duplicates are not even possible to begin with. — sstan, Aug 08 '16 at 17:13
I have updated my query to show how to deal with duplicates. — Thorsten Kettner, Aug 08 '16 at 19:48

score 1 · Accepted Answer · answered Aug 08 '16 at 13:46

You can use a union all with the type "virtual" column to get the right order, and use a filter on the returned value of the row_number analytic function to remove the duplicates while prioritizing the first query's rows:

with cte as (
          SELECT a.poclcdde,
                 a.poclnpol,
                 a.poclcdcl,
                 1 AS TYPE
            FROM dtpocl a
           WHERE     a.poclcdcl IN (216450,
                                    562223,
                                    250056,
                                    202153,
                                    8078)
                 AND POCLCDCE = 0
                 AND POCLTPSG = '01'
          UNION ALL
          SELECT a.poclcdde,
                 a.poclnpol,
                 a.poclcdcl,
                 2 AS TYPE
            FROM dtpocl a
           WHERE     a.poclcdcl IN (216450,
                                    562223,
                                    250056,
                                    202153,
                                    8078)
                 AND POCLTPSG = '02'
)
select poclcdde, poclnpol, poclcdcl
  from (select t.*,
               row_number() over (
                 partition by t.poclcdde, t.poclnpol, t.poclcdcl
                     order by t.type) as rn
          from cte t)
 where rn = 1
 order by type

Thanks @sstan. I get a "virtual" row order. But how can I confirm this? Doesn't `UNION ALL` return a random row order? — milheiros, Aug 08 '16 at 16:40
Yes, but that's why I have the `ORDER BY type` clause in the outer query, to ensure that rows from the 1st query always appear *before* rows from the 2nd query. That is what you were looking for, right? — sstan, Aug 08 '16 at 17:07

ORACLE SQL How to implement a UNION clause but maintain the order of each SELECT

3 Answers3