Getting limited result from oracle in order

Question

I have a set of 4000 records,I am only allowed to retrieve 300 records.

I am doing pagination on the resultset, but since we are limiting the result to 300 i am getting different results for each DB hit.

So is there any option to get the ordered first 300 of the 4000 records, without getting the entire result of 4000 records.

Below is the query:

select id from table where name='ronaldo' and rownum <= 300 order by id asc;

Answer 1

The problem is the query is processed in the following order:

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

For that reason, your query as written will return a random 300 records.

You'll need to do a nested query, something like this:

select * from
  (select * from table
   where name='ronaldo' order by id asc) 
where ROWNUM <= 300;

Also, if you're using Oracle 12c or higher, you can use the completely non-standard FETCH FIRST syntax:

SELECT *
  FROM table
  WHERE name='ronaldo'
  ORDER BY id asc
  FETCH FIRST 300 ROWS ONLY;

Why they don't support the LIMIT keyword? Because they're Oracle.