CODEWITHSUNDEEP
sqloracle-database
komedit1
komedit1

Reputation: 255

Oracle UNION ALL preservation of the order of records in query

I have the following scenario(table below) where I would like to pick 'X' or 'Y' based on whether they are NULL or not.

  X         Y            pick
  null     not null        Y
  not null not null        X
  not null null            X

the rows containing the data 'X' and 'Y' or UNION ALLed like below:

  select 'X' as a
  union all
  select 'Y' as a

So I tried and got the following SQL but not sure about the "rownum<=1" part. This will work(for the case where both X and Y are not null) only if UNION ALL preserve the order in which I query the two rows.

  select a from 
  (
  select 'X' as a
  union all
  select 'Y' as a
  ) where a is not null and rownum<=1;

  select a from 
  (
  select null as a
  union all
  select 'Y' as a
  ) where a is not null and rownum<=1;

  select a from 
  (
  select 'X' as a
  union all
  select null as a
  ) where a is not null and rownum<=1;

Is the above right way to go about this?. Any insight would be much appreciated

Upvotes: 0

Views: 6689

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

If you want a specific order in SQL, you have to ask for it. You could probably extend your union as:

  select a from (
    select a,rownum as rn from 
    (
       select 'X' as a, 0 as Priority from dual
       union all
       select 'Y' as a, 1 as Priority from dual
    ) where a is not null order by Priority
  ) where rn = 1

Although I admit I'm not great with Oracle. I believe rownum does odd things in WHERE clauses, that's why I've introduced an extra level of querying.

Upvotes: 1

Tim
Tim

Reputation: 8921

Coalesce

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions023.htm

Upvotes: 0

Related Questions