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;