title: Need rownum to be the same for for each record between two queries that query the same tables
body: <p>I have two tables that store user action data (almost exact same structure). Users could be in one of the tables but not the other or in both tables (or in neither). </p>
  
  <p>I have two queries, which query both tables via UNION ALL. I need the same number of records in each query, with the ROWNUM the same per user record between both data sets. </p>
  
  <p>I reverse the union between the two queries, order them the same. However, the rownums are different even though the data should be retrieved in the same order. Any insight would be greatly appreciated. </p>
  
  <p>(I'm feeding this data into a graph\chart component in which ROWNUM is required to work properly)</p>
  
  <p>Query 1</p>
  
  <pre><code>SELECT ROWNUM, cnt, opno FROM ( 
     SELECT SUM(fieldct) cnt, opno 
     FROM operchgeedp 
     WHERE opno &lt;  997 
     AND LOWER(prognm) = 'eedpmain.exe' 
     AND TRUNC(begdtt) BETWEEN DATE'2019-02-01' 
     AND  TRUNC(SYSDATE) 
     GROUP BY opno 
     UNION ALL 
     SELECT 0 cnt, opno 
     FROM operchgprv 
     WHERE opno NOT IN (
        SELECT opno 
        FROM operchgeedp 
        WHERE opno &lt;  997 
        AND LOWER(prognm) = 'eedpmain.exe' 
        AND TRUNC(begdtt) BETWEEN DATE'2019-02-01' 
        AND  TRUNC(SYSDATE) 
     )
     AND TRUNC(begdtt) BETWEEN DATE'2019-02-01' 
     AND  TRUNC(SYSDATE) 
     AND opno &lt;  997 
     GROUP BY opno )
  ORDER BY opno`
  </code></pre>
  
  <p>Query 2</p>
  
  <pre><code>SELECT ROWNUM, cnt, opno FROM ( 
     SELECT SUM(fieldct) cnt, opno 
     FROM operchgprv 
     WHERE opno &lt;  997 
     AND LOWER(prognm) = 'prvmain.exe' 
     AND TRUNC(begdtt) BETWEEN DATE'2019-02-01' 
     AND  TRUNC(SYSDATE) 
     GROUP BY opno 
     UNION ALL 
     SELECT 0 cnt, opno 
     FROM operchgeedp 
     WHERE opno NOT IN (
        SELECT opno 
        FROM operchgprv 
        WHERE opno &lt;  997 
        AND LOWER(prognm) = 'prvmain.exe' 
        AND TRUNC(begdtt) BETWEEN DATE'2019-02-01' 
        AND  TRUNC(SYSDATE) 
     ) 
     AND TRUNC(begdtt) BETWEEN DATE'2019-02-01' 
     AND  TRUNC(SYSDATE) 
     AND opno &lt;  997 
     GROUP BY opno )
  ORDER BY opno`
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/KXctl.jpg" alt="Query1"></p>
  
  <p><img src="https://i.stack.imgur.com/NwnXL.jpg" alt="Query 2"></p>