Query with ROWNUM returns differents rows on every execution

Question

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 <= 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?

Answer 1

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 <= 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;

Answer 2

ROWNUM is the wrong way of doing this kind of query. As @Kris Johnston explains in a comment with the "cart and the horse" simile: The WHERE clause is executed before the ORDER BY, and you can't guarantee the order of the results without the ORDER BY, so yes, different results per run would be expected.

You can use ROW_NUMBER() and a subquery instead:

SELECT
    t.ZRESUBDIV,
    t.SUBDIV_NAME
FROM
  ( SELECT 
        t1.ZRESUBDIV,
        t1.SUBDIV_NAME,
        ROW_NUMBER() OVER (ORDER BY t1.SUBDIV_NAME) AS RN
    FROM M_STAGE_WP_PFUNCT_LOC t1
    WHERE --- your filter goes here but NOT any ROWNUM
    GROUP BY
        t1.ZRESUBDIV,
        t1.SUBDIV_NAME
  ) t
WHERE
    t.RN <= 200
ORDER BY
    t.SUBDIV_NAME ;

The internal ORDER BY defines which rows will be selected (as the first 200 will have row_numbers from 1 to 200) and the external ORDER BY, wcich is optional, will only matter for the presentation order.

Note that the ordering is not total, i.e. you may have rows with same SUBDIV_NAME. If you want the order to be totally specified, you can change those ORDER BY to ORDER BY SUBDIV_NAME, BYZRESUBDIV (for example).

The other 2 queries in @Balazs Papp's answer are also fine, if not better. The first is probably widely used as it works in older versions, before the ROW_NUMBER() was implemented. The 2nd is surely the most concise you can get, as long you are in 12c version.