Alternatives to LIMIT and OFFSET for paging in Oracle
You will need to use the rownum
pseudocolumn to limit results. See here:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
Apply OFFSET and LIMIT in ORACLE for complex Join Queries?
You can use Analytic functions such as ROW_NUMBER()
within a subquery for Oracle 11g
assuming you need to get the rows ranked between 3rd and 8th in order to capture the OFFSET 3 LIMIT 8
logic within the Oracle DB(indeed those clauses are included for versions 12c+
), whenever the result should be grouped by CREATE_DATE
and ordered by the ID
of the departments :
SELECT q.*
FROM (SELECT DEPT.ID rowobjid,
DEPT.CREATOR createdby,
DEPT.CREATE_DATE createddate,
DEPT.UPDATED_BY updatedby,
DEPT.LAST_UPDATE_DATE updateddate,
DEPT.NAME name,
DEPT.STATUS status,
statusT.DESCR statusdesc,
REL.ROWID_DEPT1 rowidDEPT1,
REL.ROWID_DEPT2 rowidDEPT2,
DEPT2.DEPT_FROM_VAL parentcid,
DEPT2.NAME parentname,
ROW_NUMBER() OVER (PARTITION BY DEPT.CREATE_DATE ORDER BY DEPT.ID) AS rn
FROM TEST.DEPT_TABLE DEPT
LEFT JOIN TEST.STATUS_TABLE statusT
ON DEPT.STATUS = statusT.STATUS
LEFT JOIN TEST.C_REL_DEPT rel
ON DEPT.ID = REL.ROWID_DEPT2
LEFT JOIN TEST.DEPT_TABLE DEPT2
ON REL.ROWID_DEPT1 = DEPT2.ID) q
WHERE rn BETWEEN 3 AND 8;
which returns exactly 6(8-3+1) rows. If you need to include the ties(the equal values for department identities for each creation date), ROW_NUMBER()
should be replaced with another window function called DENSE_RANK()
as all other parts of the query remains the same. At least 6 records would return in this case.
How do I limit the number of rows returned by an Oracle query after ordering?
You can use a subquery for this like
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.
Update:
To limit the result with both lower and upper bounds things get a bit more bloated with
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(Copied from specified AskTom-article)
Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
See this answer for more examples. Thanks to Krumia for the hint.
How to add offset in a select query in Oracle 11g?
You can do it easily on 12c
by specifying OFFSET
.
In 12c
,
SELECT val
FROM table
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
To do the same on 11g
and prior, you need to use ROWNUM
twice, inner query
and outer query
respectively.
The same query in 11g
,
SELECT val
FROM (SELECT val, rownum AS rnum
FROM (SELECT val
FROM table
ORDER BY val)
WHERE rownum <= 8)
WHERE rnum > 4;
Here OFFSET
is 4.
Oracle 11g: LIMIT OFFSET on GROUPED data
This is the widely known question and probably a lot of answered duplicates exist, but anyway in your case this query should work:
select * from (
select rownum offset, rs.* from (
SELECT MAX(t.category) as category,
COUNT(t.category) as count
FROM tb_test_1 t
GROUP BY t.category
/* add order by clause here if needed */
) rs
) where rownum <= 10 /* limit */
and offset >= 0 /* offset */
Best practice for pagination in Oracle?
If you're already using analytics (ROW_NUMBER() OVER ...
) then adding another analytic function on the same partitioning will add a negligible cost to the query.
On the other hand, there are many other ways to do pagination, one of them using rownum
:
SELECT *
FROM (SELECT A.*, rownum rn
FROM (SELECT *
FROM your_table
ORDER BY col) A
WHERE rownum <= :Y)
WHERE rn >= :X
This method will be superior if you have an appropriate index on the ordering column. In this case, it might be more efficient to use two queries (one for the total number of rows, one for the result).
Both methods are appropriate but in general if you want both the number of rows and a pagination set then using analytics is more efficient because you only query the rows once.
Query with offset returns overlapping data sets
In Oracle, if you make "order by" to a column containing same values (like you have - 'A', 'A', 'A' ...) the order of records inside 'A' values will be random.
Please try to change your queries to ... order by "APPROVER", rowid
...
Related Topics
How to Version Your Database Schema
Extract Date (Yyyy/Mm/Dd) from a Timestamp in Postgresql
SQL Server Loop - How to Loop Through a Set of Records
Sql: How to Properly Check If a Record Exists
Syntax of For-Loop in SQL Server
Why Can't I Use Alias in a Count(*) "Column" and Reference It in a Having Clause
How to Store Historical Records in a History Table in SQL Server
What Does a Transaction Around a Single Statement Do
Do Database Transactions Prevent Race Conditions
How to Create a Pivottable in Transact/Sql
Should I Design a Table with a Primary Key of Varchar or Int
Generate Delete Statement from Foreign Key Relationships in SQL 2008
Join Multiple Tables with Active Records
How to Scale Pivoting in Bigquery
Sorting Null Values After All Others, Except Special
Why Use a Join Clause Versus a Where Condition
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows