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

Question

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

and

which way do you think is the best?

http://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle — a'r, Dec 21 '10 at 16:33
possible duplicate of [Limiting returned record from SQL query in Oracle](http://stackoverflow.com/questions/486452/limiting-returned-record-from-sql-query-in-oracle) — Bob Jarvis - Reinstate Monica, Dec 22 '10 at 02:33

score 6 · Accepted Answer · answered Dec 21 '10 at 16:37

6

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

answered Dec 21 '10 at 16:37

This is right, but you must be sure ordering is done before rownum evaluation. Anyway the great answer has already been provided in the comment: http://stackoverflow.com/questions/486452/limiting-returned-record-from-sql-query-in-oracle – andr Dec 22 '10 at 07:49

score 3 · Answer 2 · answered Nov 19 '14 at 15:16

3

Since Oracle 12c you can also use fetch first row only.

First row:

select * from example fetch first row only;

First 10 rows:

select * from example fetch first 10 rows only;

answered Nov 19 '14 at 15:16

Peter

582
5
12

score 2 · Answer 3 · edited Sep 21 '16 at 16:10

There is however a downside to this. When you want to list record in descending or ascending order you might just miss some records. Let's say you limit your return to 8 rows, then the query takes the first 8 occurrences and list them in your sort order.

The following query will grab the firs 8 records in the table that match your criteria and sort it in your order. eg.

select 
    order, order_date, booked_out_date 
from 
    orderdb
where 
    booked_out_date is not null 
    and rownum <= 8 
order by 
    booked_out_date;

The next query will first grab all the records that match my criteria, sort it and then it will display the first eight orders booked out in date order.

Select * 
From
    (Select 
         order, order_date, booked_out_date 
     From 
         orderdb
     Where 
          booked_out_date is not null 
     Order By date)
Where 
    rownum <= 8;

If orders that were booked out fall within the first eight orders but their record in the table is ie. the 20th record, you might miss it with the first query, but the second will pick it up.

score 1 · Answer 4 · edited May 23 '17 at 12:10

1

First, are you paginating and thus requiring limited rows (which is a valid result limiter)? Rather, ask yourself if your query is too far-reaching for the task at-hand.

As mentioned you can use rownum to limit the result set, or wrap rownum as below for an example of pagination:

Alternatives to LIMIT and OFFSET for paging in Oracle

edited May 23 '17 at 12:10

Community

1
1

answered Dec 21 '10 at 16:51

Jé Queue

9,627
12
50
60

score 0 · Answer 5 · answered May 22 '17 at 09:06

This is working for me. You can implement this for pagination also.

SELECT
    *
FROM
    (
        SELECT
            inner_query.*,
            ROWNUM rnum
        FROM
            (
                SELECT
                    *
                FROM
                    table_name
                ORDER BY column_name
            ) inner_query
        WHERE
            ROWNUM <= 20
    )
WHERE
    rnum >= 1;

where rownum is no. of records and rnum is start position.

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

5 Answers5