title: Oracle UNION ALL preservation of the order of records in query
body: <p>I have the following scenario(table below) where I would like to pick 'X' or 'Y' based on whether they are NULL or not.</p>
  
  <pre><code>  X         Y            pick
    null     not null        Y
    not null not null        X
    not null null            X
  </code></pre>
  
  <p>the rows containing the data 'X' and 'Y' or UNION ALLed like below:</p>
  
  <pre><code>  select 'X' as a
    union all
    select 'Y' as a
  </code></pre>
  
  <p>So I tried and got the following SQL but not sure about the "rownum&lt;=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.</p>
  
  <pre><code>  select a from 
    (
    select 'X' as a
    union all
    select 'Y' as a
    ) where a is not null and rownum&lt;=1;
  
    select a from 
    (
    select null as a
    union all
    select 'Y' as a
    ) where a is not null and rownum&lt;=1;
  
    select a from 
    (
    select 'X' as a
    union all
    select null as a
    ) where a is not null and rownum&lt;=1;
  </code></pre>
  
  <p>Is the above right way to go about this?. Any insight would be much appreciated</p>