Oracle SQL equivalent to SQLserver TOP

In SQLserver, you can issue the following code to only return the TOP n rows.

Eg. To return the first 300 rows from a search table (to return to a webpage) order by price (lowest first) where suburb is rich and transport_rating is less than 3

select TOP 300 * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price

In Oracle, there is no TOP function to perform this, the simplest method is wrap the SQL returning the rowset as an inline view and use ROWNUM to limit, the same requirement as above:

select * from
(select * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price)
where ROWNUM <=300

Both SQLserver and Oracle have HINTs which you can hint to the database which type of plan to execute the SQL. In most cases, you want the rows back as fast as possible (for websites).
So for the SQLserver code add the following OPTION(FAST n) HINT. This hint will tell SQLserver you want the first n rows back as fast as possible. This normally means that SQLserver will choose to do NESTED LOOP joins over MERGE or HASH JOINs.

select TOP 300 * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price
OPTION(FAST 300)

In Oracle there is something similar called FIRST_ROWS, you can either set it at the database level or for individual sessions or in individual SQL. The Oracle SQL code is rewritten as follows,

select * from
(select /*+ FIRST_ROWS */ * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price)
where ROWNUM <=300

You can also pass the number of rows to Oracle as well as follows:

select * from
(select /*+ FIRST_ROWS(300) */ * from search
where suburb = 'Rich'
and transport_rating <= 3
order by price)
where ROWNUM <=300

I find the SQLserver method cleaner, however getting used to Oracle INLINE views opens up a world of powerful options.

Have Fun

Paul

p.s. I thought I would start to post more often. I am not sure I can do daily however that is plan for the next 30 days.