How to Select Top 100 rows in Oracle?

Making sense of ROWNUM and ordering

ROWNUM is a peculiar beast; all unicorns and rainbows until you consider ordering. Oracle assigns ROWNUM before sorting if you place the ORDER BY clause outside the subquery. Therefore, make sure to order your data inside a subquery.

Why order inside a subquery?

Without a subquery, you’ll end up with ROWNUM assignments that happen before sorting. This unpredictability arises because Oracle allocates ROWNUM values before any sorting occurs. Always remember to apply ORDER BY within the subquery itself.

Showing the latest orders from clients

Say you're onto the "latest and greatest" - you want the latest order for each client, but limit to top 100 records. Fear not, employ the ROW_NUMBER() magic to make it happen:

-- "Everyone gets a number! But only the first 100 get to play."
SELECT * FROM (
  SELECT client_id, order_id, create_time, 
         ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY create_time DESC) as rn
  FROM orders
) WHERE rn = 1 AND ROWNUM <= 100;


Rocking FETCH FIRST in Oracle 12c

If you're on Oracle 12c, there's a special treat for you. FETCH FIRST is a cleaner, more efficient way to limit results, so your queries can be sleek, like a sports car:

SELECT client_id, order_id, create_time
FROM orders
ORDER BY create_time DESC
FETCH FIRST 100 ROWS ONLY;


Getting specific with min/max functions

In need to gather some specifics? The first or last for each group, perhaps? min() or max() to the rescue:

SELECT client_id, MIN(order_id) as first_order_id
FROM orders
GROUP BY client_id
FETCH FIRST 100 ROWS ONLY;


Optimization alert

When flexing with these techniques, ponder about your database. Specifically, if it's indexed on the columns used for sorting and partitioning, you're steering clear of any unnecessary performance hiccups.

Dealing with ancient Oracle versions

If you're strutting around with older Oracle versions, following the legacy methods to restrict rows like using ROWNUM after ORDER BY could walk you into errors. Remember, ROWNUM becomes all sneaky and assigns values before the sorting, not after:

-- "Going old school, are we?"
SELECT * FROM (
  SELECT column_name, ROWNUM as rn
  FROM (SELECT column_name FROM your_table ORDER BY ordering_column)
) WHERE rn <= 100;


Moral of the story - attend to your query structure. Ordering must always precede ROWNUM application.

Getting complex? No fret!

Complex scenarios drag additional filter conditions or multiple sort keys into the picture. No need to shake in your boots, just keep your wits about you. Fulfill these requirements within the subqueries before implementing the ROWNUM or FETCH FIRST limiter.

Digging deeper into row limiting

Looking to up your game in row limiting? Listed below are some references that can strengthen your muscles in taming the Oracle's monster of row limiting.