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

How to limit the number of records returned by an ORACLE qu开发者_如何学Goery?

and

which way do you think is the best?

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

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;

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.

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

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.