0

The below query returns me 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. Please advise. Thanks

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
challengeAccepted
  • 7,106
  • 20
  • 74
  • 105

1 Answers1

0

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')
ekochergin
  • 4,109
  • 2
  • 12
  • 19