How do I limit the number of rows returned by an Oracle query after ordering?

Limiting Rows: Oracle 12c onwards

From Oracle version 12c, Oracle introduced the OFFSET and FETCH clauses. This is a more straightforward way to limit output rows:

-- You know you've worked too long when you read "OFFSET" and think of barbecue.
SELECT your_columns 
FROM your_table
ORDER BY your_order_column
OFFSET x ROWS FETCH NEXT y ROWS ONLY;


It's like telling the software, "Jump x rows, then give me y rows". It's perfect for pagination.

Dealing with duplicate values

Sometimes, you might end up with rows having the same values for the column you're ordering by. The FETCH clause has the WITH TIES option to deal with this:

-- When the race is too tight, everyone who ties wins!
SELECT your_columns
FROM your_table
ORDER BY your_order_column
FETCH FIRST n ROWS WITH TIES;


Going by Percentages

Oracle 12c also allows you to fetch a top percentage of rows:

-- Sometimes, it's all about the top percent.
SELECT your_columns
FROM your_table
ORDER BY your_order_column
FETCH FIRST x PERCENT ROWS ONLY;


For versions older than Oracle 12c

Before Oracle 12c, life wasn't as easy. You'd have to leverage hierarchical queries. Here's how you can do it with ROWNUM:

-- It's not as easy, but hey, at least it works!
SELECT * FROM (
  SELECT your_columns, ROWNUM AS rnum FROM (
    SELECT your_columns FROM your_table ORDER BY your_order_column
  )
) WHERE rnum BETWEEN offset_values AND offset_values + fetch_interval;


Using analytic functions

The ROW_NUMBER() analytic function assigns unique row numbers according to sorting:

-- Ninja mode: ORDER BY before limitation
SELECT your_columns 
FROM (
    SELECT your_columns, 
           ROW_NUMBER() OVER (ORDER BY your_order_column) AS rn
    FROM your_table
)
WHERE rn BETWEEN start_row AND end_row;


It's a touch more sophisticated, but it gives you great control over the rows you're fetching.

Mimicking OFFSET

By combining ROWNUM with subqueries, you can create a pseudo OFFSET:

-- Offsetting manually, because we're cool like that.
SELECT * FROM (
  SELECT a.*, ROWNUM rnum FROM (
    SELECT your_columns FROM your_table ORDER BY your_order_column
  ) a
  WHERE ROWNUM <= (starting_offset + number_of_rows)
)
WHERE rnum > starting_offset;


Be mindful of large data sets

For big data sets, consider:

• Using cursor-based pagination with OFFSET and FETCH.
• Prefetching rows and storing keys in a temp table for faster access.
• Avoiding complex joins and subqueries that could slow down paginated results.

Choose the appropriate method for your needs

Your specific scenario determines the best approach:

• For precise row access, use ROWNUM with an inner subquery.
• For pagination, OFFSET and FETCH are your friends in Oracle 12c onwards.
• If tied results need to be included as well, go for FETCH FIRST ... WITH TIES.