Query with ROWNUM returns differents rows on every execution I have a query that is used to search for a list of sub divisions in our network. When the query is executed without any filter, it returns a different amount of rows on every execution

SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
WHERE ROWNUM &lt;= 200  -- normally a filter goes here
ORDER BY t1.SUBDIV_NAME


Execution 1


ZRESUBDIV    SUBDIV_NAME                                                
------------ ------------------------------------------------------------
262          ALEXANDRIA                                                  
400          ALLANWATER                                                  
671          ASHCROFT                                                    
372          BALA                                                        
277          BEACHBURG                                                   
200          BRIDGE                                                      
374          CARAMAT                                                     
543          CENTRAL BUTTE                                               
208          CHAPAIS                                                     
324          CHATHAM                                                     
340          CHICAGO                                                     
...


Execution 2


ZRESUBDIV    SUBDIV_NAME                                                
------------ ------------------------------------------------------------
670          ALBREDA                                                     
262          ALEXANDRIA                                                  
400          ALLANWATER                                                  
671          ASHCROFT                                                    
372          BALA                                                        
277          BEACHBURG                                                   
604          BLACKFOOT                                                   
200          BRIDGE                                                      
607          CAMROSE                                                     
354          CASO                                                        
543          CENTRAL BUTTE                                               
...


There is also missing data: the subdivision Baton Rouge never appears although it is in the data.

If I remove the ROWNUM, all data is correctly returned. I need to leave ROWNUM since the query support a WHERE clause with a filter on the subdivision name.

What is the cause of this?
ROWNUM is evaluated before ORDER BY, that is not how ROWNUM should be used. To use ROWNUM properly, use a query like this:

SELECT * FROM 
(
  SELECT DISTINCT 
      t1.ZRESUBDIV,
      t1.SUBDIV_NAME
  FROM M_STAGE_WP_PFUNCT_LOC t1
  ORDER BY t1.SUBDIV_NAME
)
WHERE ROWNUM &lt;= 200;


But this is old. Since you are on 12c, use this:

SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
ORDER BY t1.SUBDIV_NAME
FETCH FIRST 200 ROWS ONLY;