Get range of records from SQL query Oracle

oracle, oracle10g, oracle11g, sql

Solution

`rownum` is applied before the `ORDER BY` so your query is almost certainly not doing what you expect. Your query is essentially asking for an arbitrary 3 rows and the `ORDER BY` isn't doing anything useful.

You could use the analytic function `row_number` instead, i.e.

SELECT *
  FROM (SELECT a.*,
               row_number() over (order by userid asc) rn
          FROM activeSessionsLog a)
 WHERE rn BETWEEN 8 AND 10

which will page through the results

SQL> ed
Wrote file afiedt.buf

  1  select empno, ename, job
  2    from (select e.*,
  3                 row_number() over (order by empno) rn
  4            from emp e)
  5*  where rn between 1 and 3
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN

SQL> ed
Wrote file afiedt.buf

  1  select empno, ename, job
  2    from (select e.*,
  3                 row_number() over (order by empno) rn
  4            from emp e)
  5*  where rn between 4 and 8
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST

SQL> ed
Wrote file afiedt.buf

  1  select empno, ename, job
  2    from (select e.*,
  3                 row_number() over (order by empno) rn
  4            from emp e)
  5*  where rn between 9 and 11
SQL> /

     EMPNO ENAME      JOB
---------- ---------- ---------
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK

It may be more efficient, however, to do something like this where Oracle can use the inner `rownum <= 10` predicate to know that it can stop sorting the data once it has identified the first 10 rows.

SELECT c.*
  FROM (SELECT b.*, rownum rn
          FROM (SELECT a.*
                  FROM activeSessionsLog a
                 ORDER BY userid asc) b
         WHERE rownum <= 10) c
 WHERE rn >= 8

Problem

I want to use this SQL query to get only the records between 8 and 10: ``` select * from( SELECT a.*,rownum rn FROM ACTIVESESSIONSLOG a ORDER BY USERID ASC) WHERE rn >= 8 and rn <= 10 ``` When I implement this SQL query into pagination I get every time 1 row on the second page no matter how many rows I have configured to be displayed into the pages. Is this SQL query valid? This is the table structure: ``` -- TABLE ACTIVESESSIONSLOG CREATE TABLE ACTIVESESSIONSLOG( ASESSIONID VARCHAR2(30 ) NOT NULL, USERID VARCHAR2(30 ), ACTIVITYSTART TIMESTAMP(6), ACTIVITYEND TIMESTAMP(6), ACTIVITY CLOB ) / ``` Best wishes

Original source