Limiting Returned Record from SQL Query in Oracle

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 limit the results to 1 row in Oracle SQL

In Oracle you need to do the ordering first and then select rownum. Thus, you need to nest the query which returns the sorted data and take the filtering WHERE clause outside.

SELECT * FROM
(
 SELECT customerNumber
 FROM ORDERS 
 GROUP BY customerNumber 
 ORDER BY count(orderNumber) DESC
) resultSet
WHERE ROWNUM=1;

limit' clause in Oracle SQL SQL command not properly ended

Generally, we use LIMIT in MYSQL database and Rownum in Oracle.

MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle Syntax:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

References:

https://www.w3schools.com/sql/sql_top.asp

How to limit the number of records returned by an ORACLE query?

Add "where rownum <= # of entries" at the end of your query.

Query limit in oracle database

FETCH FIRST is only available since Oracle 12c.

For the rownum approach, use a subquery that contains order by, then limit the rows in the enclosing query:

SELECT * FROM (
    SELECT * FROM ALARMS WHERE OBJECT_ID=0 AND TIMESTAMP<=152567750417
    ORDER BY TIMESTAMP DESC
) dt
WHERE ROWNUM<=51 

Limit the number of rows returned from a query in both oracle and postgres

Both database systems support the ANSI standard fetch first:

select *
from some_table
order by something
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;

Oracle started supporting that with 12.1

• fetch first in the Postgres manual
• fetch first in the Oracle manual

How to LIMIT query results in Oracle 11g?

You need to add an ORDER BY clause to sort your entries from highest to lowest and then you can use an outer SELECT to get the first entry out of it:

SELECT *
FROM 
(SELECT p.product_name, SUM(o.quantity) no_of_product_sale
FROM Order_Address_Quantity o
JOIN Product p ON p.product_code = o.product_id
GROUP BY p.product_name
ORDER BY 2 DESC )
WHERE ROWNUM = 1;

How to limit number of groups returned in a query, but not the number of rows in Oracle

I believe you may be looking for something like this:

select *
from   mytable
where  id in (
               select distinct id
               from   my_table
               where  my_date between x and y
               fetch  first :n rows only
             )
;

:n is a bind variable, encoding the number of groups you want to select.

This should be more efficient than solutions using analytic functions - even if it must read the base table twice. In tests posted on OTN, I showed that the difference is not small.

EDIT If I remember correctly, FETCH is not implemented in the most efficient way (perhaps for good reasons, having to do with features we don't need in this query - such as how to deal with ties). FETCH itself resembles a DENSE_RANK() implementation rather than the faster row limiting clause (using ROWNUM). I would likely need to modify the query to do away with FETCH, if speed was really important. END EDIT

Further edit to do with performance comparisons

Frequent poster MT0 requested a pointer for the claim that aggregate solutions can (and often are) more efficient than analytic function approaches, even when the former may require multiple passes through the data where the analytic function approach requires only one.

Alas, OTN (what now calls itself the "Oracle Groundbreakers Developer Community", the discussion board hosted by Oracle itself) went through a massive - and massively botched - platform change at the end of September 2020; that messed up both the search facilities and the formatting of old posts, to the point of rendering them almost unusable.

Instead, I will show here a simple mock-up of the OP's problem in this thread; code that anyone can run so they can repeat the tests on their own machine.

I created a table with two columns, ID and STR - the ID plays the same role as in the OP's question, and STR is just extra payload to mimic real-life data. ID is number and STR is varchar2(100). I populated the table with 9 million rows - 1 million ID's, nine rows for each ID. The task is to select just three "groups" (three distinct ID's, then select all the rows from the base table for those three distinct ID's).

With no index on the ID column, the aggregate solution runs in 0.81 seconds on my machine; with an index on ID, it runs in 0.47 seconds. The analytic functions solution runs in 0.91 seconds, with or without an index (obviously - there is no way an index can benefit the analytic function solution). All these results are for column ID not declared NOT NULL.

Here is the code to create the table, the index on ID, and the two queries I tested. Note: As I explained in my first edit (above), fetch is slow; I replaced it with a standard row-limiting technique using ROWNUM in an over-query.

drop table t purge;

create table t (id number, str varchar2(100));

insert into t
  with    row_gen as (select level from dual connect by level <= 3000)
  select  mod(344227 * rownum, 1000000), rpad('x', 100, 'x')
  from    row_gen cross join row_gen
;

commit;

create index t_idx on t(id);

select *
from   t
where  id in (
  select id from (select distinct id from t)
  where  rownum <= 3
);

select *
from   ( select t.*, dense_rank() over (order by id) dr from t )
where  dr <= 3;

Limit Clause in Oracle 11g

In older versions of Oracle, you need a subquery:

select c.*
from (select c.*, row_number() over (order by c.points desc) as seqnum
      from customers c
     ) c
where seqnum = 2;

You will see examples that use rownum in the outer query:

select c.*
from (select c.*
      from customers c
     ) c
where rownum = 2;

However, that does not work, because rownum is incremented only when rows are placed in the result set. Window functions are the simplest solution in older versions of Oracle (new versions support fetch/offset).

---

Related Topics