How can i avoid rownum as a column when limiting records in oracle?

Question

when i run the query:

select * 
  from ( select a.*, 
                ROWNUM rnum 
           from ( select * 
                    from test 
                   order by null ) a  
          where ROWNUM <= 2000 )
  where rnum  >=1

I'm getting all the columns along with rownum as a separate column which I don't want, How to achieve this or is there any way to limit records?

Answer 1

Since the final filter is for ROWNUM >= 1 (which will always be true), you can eliminate that and just use:

select * 
from   (
  select * 
  from   test 
  order by null
)  
where ROWNUM <= 2000

(Note: ORDER BY NULL will apply a non-deterministic ordering.)

If you want to specify a different starting row then you will need to specify the columns you want to return:

select col_1,
       col_2,
       -- ...
       col_n
from   (
  select a.*,
         ROWNUM rnum
  from   (
    select * 
    from   test 
    order by null
  ) a
  where ROWNUM <= 2000
)
WHERE rnum > 1000

In Oracle 12c you can use:

SELECT *
FROM   test
ORDER BY NULL
FETCH FIRST 2000 ROWS ONLY;

or

SELECT *
FROM   test
ORDER BY NULL
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;