Oracle Database Rownum Between

Question

There are 25 records in this sql query I want to get between 5 and 10. How can I do it ? I use 11g

select
(
    select count(*) as sayfasayisi
    from konular t
    where t.kategori is not null
) as sayfasayisi,
t.id,
t.uye,
t.baslik,t.mesaj,t.kategori,t.tarih,
t.edittarih,t.aktif,t.indirimpuani,t.altkategori,t.link,
nvl(
    (select case when t.id = f.konuid and f.uye = 'test' then '1' else '0' end
     from takipkonu f where t.id = f.konuid and f.uye = 'test'), '0') as takip
from konular t
where t.kategori is not null

Oracle does not assign a permanent row number to records, so if you're looking for 5th to 10th record from your table, please know that it is not guaranteed that you will get the same records every time. For your specific question here, you need to see your specific requirement for retrieving only those records. — Incognito, Jul 28 '17 at 01:41
You can use `ROW_NUMBER()` with a certain ordering and then retain only the 5th through 10th record. [See here](https://stackoverflow.com/questions/7480243/sql-oracle-order-by-and-limit). — Tim Biegeleisen, Jul 28 '17 at 01:43

score 0 · Accepted Answer · answered Jul 28 '17 at 01:47

You can use ROW_NUMBER() to assign a row number based on some ordering logic contained in your current query, e.g. a certain column. Then, retain only the 5th to 10th records:

select t.*
from
(
    select
    (
        select count(*) as sayfasayisi
        from konular t
        where t.kategori is not null
    ) as sayfasayisi,
    ROW_NUMBER() OVER (ORDER BY some_col) rn,
    t.id,
    t.uye,
    ...
) t
where t.rn between 5 and 10;

Oracle Database Rownum Between

1 Answers1