How to fecth records in Oracle 10g express edition within specific limits?

Question

I am using Oracle 10g express edition. I have linked it with codeigniter.

I want to fetch 10 records per page to show the records.

Here is the table tbl_language:-

LANGUAGE_ID | LANGUAGE_TITLE | LANGUAGE_ADD_DATE | LANGUAGE_STATUS  |
=====================================================================
      1     |     Hindi      |    18-FEB-17      |        1         |
      2     |     English    |    18-FEB-17      |        1         |
      3     |     Bangla     |    18-FEB-17      |        1         |

Now, I am using the following query

"SELECT * FROM 
       (SELECT fetch.*, rownum rnum FROM 
             (SELECT * FROM tbl_language 
                WHERE language_id > 0 
                ORDER BY TO_CHAR(language_add_date, 'YYYY-MM-DD HH24:MI:SS') 
               DESC) 
       fetch)"

It gives me the data in following manner:-

LANGUAGE_ID | LANGUAGE_TITLE | LANGUAGE_ADD_DATE | LANGUAGE_STATUS  | RNUM
===============================================================================
      3     |     Bangla     |    18-FEB-17      |        1         |   1
      2     |     English    |    18-FEB-17      |        1         |   2
      1     |     Hindi      |    18-FEB-17      |        1         |   3

Now, I am modifying the query two select specific number of rows (something like using limit)-

"SELECT * FROM 
    (SELECT fetch.*, rownum rnum FROM 
        (SELECT * FROM tbl_language WHERE language_id > 0 ORDER BY TO_CHAR(language_add_date, 'YYYY-MM-DD HH24:MI:SS') DESC) 
    fetch WHERE rownum >= 1 AND rownum < 2) "

It returns one row. Then I modified the query

"SELECT * FROM 
    (SELECT fetch.*, rownum rnum FROM 
        (SELECT * FROM tbl_language WHERE language_id > 0 ORDER BY TO_CHAR(language_add_date, 'YYYY-MM-DD HH24:MI:SS') DESC) 
    fetch WHERE rownum >= 2 AND rownum < 3) "

It returned empty results. What am I doing wrong?

Sorry, I just assumed that my answer work on everything that use SQL. I will remove that answer. Please check out this SO post: http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle — ultrajohn, Feb 19 '17 at 07:21
Possible duplicate of [Alternatives to LIMIT and OFFSET for paging in Oracle](http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle) — ultrajohn, Feb 19 '17 at 07:29
What you are doing wrong is `where rownum >= 2`. Rows are generated one at a time. First row is assigned `rownum = 1`, but then it fails the `where` condition so it is discarded. `rownum = 1` is **reassigned** to each successive row, and each row fails the `where` clause. This is why you get an empty result in the last query. To save it (although it may not do what you need), generate `rownum` and give it an alias like `rnum` in the subquery, then select `rnum` in the outer query and writhe the `where` condition in terms of `rnum`, not `rownum`. — , Feb 19 '17 at 14:59

score 0 · Answer 1 · answered Feb 19 '17 at 07:24

Here it is. The answer is in your post:

"SELECT * FROM 
    (SELECT fetch.*, rownum rnum FROM 
        (SELECT * FROM tbl_language WHERE language_id > 0 ORDER BY TO_CHAR(language_add_date, 'YYYY-MM-DD HH24:MI:SS') DESC) 
    fetch WHERE rownum >= 1 AND rownum < 11) "

I basically changed the range of rownum (i.e., [1,10]) in your condition.

How to fecth records in Oracle 10g express edition within specific limits?

1 Answers1