title: DB2 sort the result by coumn which is not mention in query
body: <p>Result of following query is weird to me, (I' m new to DB2). It sorted the result by column in the table which I didn't mention in the query.</p>
  
  <pre><code>WITH RESULT AS ( 
    SELECT T1.*, ROWNUMBER() OVER() AS RNUM 
      FROM TableNAmeT1 
     WHERE column1= 'xyz'
  ) 
  
  SELECT * 
    FROM RESULT 
   WHERE RNUM BETWEEN 1 AND 100 
   ORDER BY ORDER OF RESULT OPTIMIZE FOR 100 ROWS
  </code></pre>
  
  <p>It doesn't sort when I use following query</p>
  
  <pre><code>WITH RESULT AS ( 
    SELECT T1.*, ROWNUMBER() OVER() AS RNUM 
      FROM TableNAmeT1 
     WHERE column1= 'xyz'
  ) 
  
  SELECT * 
    FROM RESULT  
   ORDER BY ORDER OF RESULT
  </code></pre>
  
  <ul>
  <li>Is this because of the index of the table or anything else, normally these tables are FIFO</li>
  </ul>
  
  <p>**  Clearly this is due to the both “OPTIMIZE FOR 100 ROWS “ clause and use of scalar value(ROWNUMBER() for filtering (in WHERE clause).</p>
  
  <p>** If I narrow down my question further, I want to know what is reason for difference order of first query and second query.</p>
  
  <p>Thanks all</p>