• 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 in Oracle

Top-N Queries in Oracle

July 30, 2022 by techgoeasy Leave a Comment

Top-N Queries are common in Oracle database. People will often look for the below queries

oracle top 100
oracle select top 10
oracle top 1000 rows
selecting top 10 rows in oracle
how to select top 10 rows

These are essentianlly the same queries . They are called Top-N queries

Let’s take a look at the different methods in Oracle to achieve Top-N Queries in Oracle

Pre 12c

(1) Using ROWNUM in Oracle Clause

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).

Here is the way for selecting top 10 rows in oracle

The below query will give top 10 rows
SELECT *
FROM (SELECT *
FROM dept
ORDER BY sales DESC)
WHERE ROWNUM <= 10;

(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;

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


(3) Using RANK() and DENSE_RANK(): These are analytic functions 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;

(4) NTILE and Percent_rank can also be used

With 12c

(5) 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

A Top-N query allows us to retrieve the top or bottom N rows from an ordered set.

Top-N Queries in oracle

Example:

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

select * from my_test order by name fetch first 10 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

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 . 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

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

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

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

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