[SOLVED] How to get rows in the correct order when using RowNum in where clause

How to get rows in the correct order when using RowNum in where clause

The below query returns a large set of records. I only want to get 100000 as fast as possible in exactly that specific order. When I use where RowNum < 100000, the order gets messed up for obvious reasons.

How do I change the below query to return in the right orderby and only limit to get 100000 records?

SELECT S.LOGIN_ID as LoginId, S.ProductId as ProductId, P.CREATE_DT as CreateDate, 
FROM SITE_USER S
INNER JOIN Production P on P.LOGIN_ID = S.LOGIN_ID
where P.PROCESS_CD = 'REGISTRATION' 
and P.CREATE_DT >= '20-JAN-21'
order by P.Request_id asc

Solution

Since you're running on v11, you can't use FETCH but you can limit the sub-query.

select * from (
    SELECT S.LOGIN_ID as LoginId, S.ProductId as ProductId, P.CREATE_DT as CreateDate, 
      FROM SITE_USER S
     INNER JOIN Production P on P.LOGIN_ID = S.LOGIN_ID
     where P.PROCESS_CD = 'REGISTRATION' 
       and P.CREATE_DT >= '20-JAN-21'
     order by P.Request_id asc)
where rownum <= 10000;

UPD. And check please if this condition will work

and P.CREATE_DT >= '20-JAN-21'

if p.create_dt of date type, you need to update it to something similar to this

and P.CREATE_DT >= to_date('20-JAN-21', 'dd-MON-yy')