Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 03, 2020 - 6:13 pm UTC

Last updated: September 07, 2020 - 1:34 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi TOM,

Maybe it's trivial, but i cannot find the solution: after removing a string ("pqr"), i need to keep the initial order so that this:

select 'xyz' from dual
union all
select 'abc' from dual
union all
select 'pqr' from dual
minus 
select 'pqr' from dual;


would output:
----
xyz
abc


and not
-------
abc
xyz

due to MINUS automatic sort being applied.
Any suggestions ? Can this be done in SQL ?


with LiveSQL Test Case:

and Connor said...

You need something the *defines* order, because without an ORDER BY, there are no guarantees of any ordering in a SQL result.

For example, even without the minus, just doing:

select 'xyz' from dual
union all
select 'abc' from dual


does *not* imply that you would get 'xyz' back as the first row. That is totally at the whim of the database engine.

So you would need to add something that will let you sort on that, eg

SQL> select c
  2  from (
  3  select 1 s, 'xyz' c from dual
  4  union all
  5  select 2, 'abc' from dual
  6  union all
  7  select 3, 'pqr' from dual
  8  )
  9  where c not in (
 10  select 'pqr' from dual
 11  )
 12  order by s;

C
---
xyz
abc

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow-up

Alex, September 04, 2020 - 6:32 am UTC

Nice and clever!
Stepping up a little bit, the input is
"xyz abc pqr", i use a pipelined function to convert row to columns :

SELECT
   COLUMN_VALUE
FROM
   TABLE(convert_string_to_table(p_string => 'xyz abc pqr', delimiter => ' '))

i get:
----
xyz
abc
pqr

always (?) displayed in this order, how the pipelined function processed the input.
I can easily add a "rownum" for this set, so i get:
SELECT
   COLUMN_VALUE word, rownum word_priority
FROM
   TABLE(convert_string_to_table(p_string => 'xyz abc pqr', delimiter => ' '))

and get:
-----
xyz 1
abc 2
pqr 3

But.. how to do a "MINUS", as the second set would not have the same rownum
SELECT
   COLUMN_VALUE word, rownum word_priority
FROM
   TABLE(convert_string_to_table(p_string => 'xyz abc pqr', delimiter => ' '))
MINUS
SELECT 'pqr' , rownum --this # is always dynamic, so it won't get eliminated from the first set .. ?
FROM dual;

Thanks,

Follow-up

Alex, September 04, 2020 - 6:36 am UTC

Silly me, it's obvious:

SELECT word
        FROM
        (
            SELECT
                COLUMN_VALUE word, rownum word_priority
            FROM
                TABLE(convert_list_to_table(p_list => 'xyz abc pqr', p_delimiter => ' '))

        )
        where word not in (SELECT 'pqr' FROM dual)
        order by word_priority
        ;


output
-----
xyz
abc


Cheers!
Connor McDonald
September 07, 2020 - 1:34 am UTC

indeed.

Worthy of note - even a *pipelined* function does not guarantee the order in which things will come out, because what if someone ran this as part of a parallel query.

(I'm just saying ... you always need to be careful with ordering assumptions)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.