Oracle rownum in outer query giving wrong results

Question

i have a long sql query which is using rownum but giving wrong values. Below is my query:

Select *
FROM (  SELECT *
    FROM R this_
         LEFT OUTER JOIN TS
            ON this_.T1 = TS.T1
         LEFT OUTER JOIN T 
            ON TS.T2 = T.T2
         LEFT OUTER JOIN TS system4_
            ON T.SRC = system4_.system_id
         LEFT OUTER JOIN TS system6_
            ON T.TGT = system6_.system_id
         LEFT OUTER JOIN TS system7_
            ON touchpoint3_.INIT = system7_.system_id
         LEFT OUTER JOIN ST 
            ON TS_.SC = ST.SC
         LEFT OUTER JOIN RS 
            ON this_.status_id = RS.status_id
         LEFT OUTER JOIN client client5_                                             ONsystem4_.CLIENT_ID=client5_.CLIENT_ID                                  
   WHERE this_.status_id = 5
ORDER BY this_.ID --This is a column on R table
)WHERE ROWNUM <= 10

But if i put rownum in the subquery like this i get the correct output

WHERE this_.status_id = 5 and rownum<=10

Can anyone please tell me why I am getting wrong output with my query?

Answer 1

In the inner query, the test of ROWNUM <= 10 will be done before ORDER BY this_.ID is applied, so you're at the mercy of whatever order Oracle returns the data in (probably the order they appear in whatever index is used to access them.

In the outer query you have already provided an ordered set, so the result of filtering to only the first 10 rows is not guaranteed to be the same, since the order of the elements when allocating the row numbers is not the same.

The only time you should use ROWNUM to restrict the results of a query is when:

1. you don't care about which rows you get back
2. you are starting with an ordered result set

The best approach for your requirement would probably be to use the RANK or DENSE_RANK analytic functions.

A quick Google reveals that are several examples of how to do this already.