Get range of records from SQL query Oracle
Solution 1
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
Solution 2
Since Oracle 12c you can now use offset/fetch clause.
You can now write queries like:
SELECT *
FROM user_objects
ORDER object_name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Please review this new feature and also consider the improvements in the execution plan.
Peter Penzov
Updated on June 18, 2022Comments
-
Peter Penzov almost 2 years
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