0

so I have a large table that I'd like to output, however, I only want to see the rows between 20 and 30.

I tried 

select col1, col2
from table
where rownum<= 30 and rownum>= 20;

but sql gave an error

I also tried --where rownum between 20 and 30 it also did not work.

so whats the best way to do this?

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Amr Att
  • 13
  • 1
  • 2

2 Answers2

3
SELECT * 
FROM T 
ORDER BY I 
OFFSET 20 ROWS --skips 20 rows
FETCH NEXT 10 ROWS ONLY --takes 10 rows

This shows only rows 21 to 30. Take care here that you need to sort the data, otherwise you may get different results every time.

See also here in the documentation.

Addendum: As in the possible duplicate link shown, your problem here is that there can't be a row with number 20 if there is no row with number 19. That's why the rownum-approach works to take only the first x records, but when you need to skip records you need a workaround by selecting the rownum in a subquery or using offset ... fetch

Example for a approach with using rownum (for lower oracle versions or whatever):

with testtab as (
  select 'a' as "COL1" from dual
  union all select 'b' from dual
  union all select 'c' from dual
  union all select 'd' from dual
  union all select 'e' from dual
)
select * from 
(select rownum as "ROWNR", testtab.* from testtab) tabWithRownum
where tabWithRownum.ROWNR > 2 and tabWithRownum.ROWNR < 4;
--returns only rownr 3, col1 'c'
Chrᴉz remembers Monica
  • 1,829
  • 1
  • 10
  • 24
0

Whenever you use rownum, it counts the rows that your query returns. SO if you are trying to filter by selecting all records between rownum 20 and 30, that is only 10 rows, so 20 and 30 dont exist. You can however, use WITH (whatever you want to name it) as and then wrap your query and rename your rownum column. This way you are selecting from your select. Example. 

    with T as (
            select requestor, request_id, program, rownum as "ROW_NUM" 
            from fnd_conc_req_summary_v where recalc_parameters='N')
select * from T where row_num between 20 and 30;
Jett
  • 1
  • 1