CODEWITHSUNDEEP
sqloracle-databaseunion
milheiros
milheiros

Reputation: 619

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

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

Upvotes: 1

Views: 1639

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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

Upvotes: 3

sstan
sstan

Reputation: 36473

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

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132570

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

Upvotes: 3

Related Questions