• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » Top-N Queries and Pagination in Oracle

Top-N Queries and Pagination in Oracle

September 7, 2016 by techgoeasy Leave a Comment


Top-N Queries  and Pagination is common in Web-based applications. The user inputs a set of criteria, which runs a query and then lets the user click the Previous and Next buttons to page through the result set. To achieve this paging functionality, the application needs to be able to get a certain set of rows from the Database query.

Lets take a look at the different method in Oracle to achieve Top-N Queries in Oracle and Pagination in Oracle query

Pre 12c

(1) Using ROWNUM Clause

What is ROWNUM
It is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, … N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row .

Here is the way to obtain the top 5 values

SELECT *
FROM (SELECT *
FROM dept
ORDER BY sales DESC)
WHERE ROWNUM <= 5;

This version will sort Dept by sales descending and then return the first five records it encounters (the top-five records).
For pagination in oracle , if you want the 5 -10 records of the Dept order by sales desc then go for this.

SELECT a.*
FROM (SELECT ROWNUM rn, b.*
FROM ( SELECT *
FROM dept
ORDER BY sales dsc) b  where rn <=10) a
WHERE a.rn >= 5

The general syntax would be

select *
from
( select rownum rnum, a.*
from (your_query) a
where rownum <= M )
where rnum >= N;

(2) Using ROW_NUMBER() oracle analytic function: It behaves similarly to the ROWNUM pseudo column but is more flexible and has more capabilities

Here is the way to obtain the top 5 values

SELECT *
FROM (SELECT d.*,row_number() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

Here is the query for Pagination

SELECT * FROM (
SELECT
d.*,
row_number() over (ORDER BY d.sales DSC) rn
FROM dept d) WHERE rn BETWEEN 0 AND 5 ORDER BY rn;

The above Top N queries will give return different records when two things tied for the spot when using the top n queries


(3) Using RANK() and DENSE_RANK():These are analytic function which can be used to remove the problem stated above
Here is the way to obtain the top 5 values using rank

SELECT *
FROM (SELECT d.*,rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

Here is the way to obtain the top 5 values using dense_rank

SELECT *
FROM (SELECT d.*,dense_rank() over (ORDER BY d.sales DSC) rn
FROM dept d
)
WHERE rn <= 5;

With 12c

Top-N feature:

Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set or, alternatively, the first N records after skipping over a set of records, so you can easily paginate through a result set

A Top-N query allows us to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set

Top-N Queries in oracle, pagination in oracle query

Example:

SELECT value
FROM mytable
ORDER BY value DESC
FETCH FIRST 10 ROWS ONLY;  

select * from my_test order by name fetch first 3 rows only;

If you look at the optimizer plan for the above query, it is still using row_number() under the wrap for doing it

Pagination can also happen with this feature with the use offset syntax

– offset 10 rows fetch first 10 rows only

select * from my_test order by id
offset 10 rows fetch next 10 rows only;

– offset 10 rows fetch first 0.1 percent rows only

select * from my_test order by id offset 10 rows first 0.1 percent rows only;

– offset 10 rows fetch first 3 rows with ties. It means all the top rows with ties will also be included in the result

select * from my_test order by name fetch first 3 rows with ties;

If you check the optimizer plan of the above query, you will find optimizer is using rank() function as demonstrated above in Pre 12c case

Restriction
(1)If you have a SELECT statement with FOR UPDATE, you can’t use it.
(2)The SELECT statement can’t CURRVAL or NEXTVAL of sequences
(3) If the query of the Materialized Views has this clause, then you can’t do an incremental refresh of that Materialized View

Hope you like the article on Top-N Queries in oracle and Pagination in oracle query. Please do provide feedback

Also Reads
Lead Function in Oracle
RANK function in Oracle
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html


Filed Under: Oracle, Oracle Database, Oracle Sql Tagged With: Top-N Queries and Pagination

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • how to check all constraints on a table in oracle
  • Oracle dba interview questions and answers
  • Oracle Indexes and types of indexes in oracle with example
  • Query to check table size in Oracle database



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us