2 Amazing Ways to Limit Rows in Oracle SQL Query

Here are two top ideas to limit the rows in Oracle. Those are ROWNUM and the Fetch. Below, you will find examples on usage.

Idea#1 – ROWNUM

Here is an SQL query to get a row using the ROWNUM. ROWNUM returns a number that indicates the position of a row in the table.

Precisely, the first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. You can use ROWNUM in the WHERE clause of a SELECT statement to limit the number of rows retrieved.

select * from emp where rownum = 1
select * from emp where rownum < 3

Idea#2 – Fetch

Indeed, the recent development in Oracle 12c is the row limiting clause lets us easily specify how many results to return from a query. We can choose either several rows or a percentage of rows. The number can be exact.

Even more important, the rows returned by the row limiting clause will respect the order-by-clause if there is one. Without the order by clause, the syntax will return the first N rows it finds. For example, let’s get the first three satellites based on their launch date.

--First 3 satellites.
select
   to_char(launch_date, 'YYYY-MM-DD') launch_date,
   official_name
from satellite
join launch
   on satellite.launch_id = launch.launch_id
order by launch_date, official_name
fetch first 3 rows only;

Related