0

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?

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
svsLm
  • 127
  • 1
  • 4
  • 12

1 Answers1

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Got it, I just want to know other than using rownum is there any option for limiting records? @MT0 – svsLm May 17 '17 at 09:35
  • @svsLm Depends on your version - I have just updated for Oracle 12c. You could also use the `ROW_NUMBER()` analytic function - see [this question](http://stackoverflow.com/q/470542/1509264) for other options/examples. – MT0 May 17 '17 at 09:38