How to oracle SQL limit rows? 6 ways

In SQL, there are several ways to Oracle SQL limit rows. Limiting rows or selecting the top-most record improves the performance of the query. Also, showing millions of records does not give any good information to users. Therefore, limiting rows in Oracle SQL is useful for programming.

I hope to list everything below. Performance can usability of the query, and according to the pattern of SQL(How To Performance Tuning In Oracle | 10 Tips). I plan to explain about below

• ROWNUM
• FETCH FIRST
• FETCH NEXT and OFFSET
• LIMIT
• TOP
• ROW_NUMBER

The above keywords are used to test the SQL Oracle limit rows. But some are not relevant to Oracle. Let’s see what they are.

Oracle SQL limit rows Examples

ROWNUM is the most straightforward way of limiting the rows returned by a database table. This is also free to check if at Least One Row Exists in the table according to the query.

ROWNUM

ROWNUM is not used in all the databases, but if it is there, then that is the best way to check it. This is used to limit the return record count by adding ROWNUM 5. If ROWNUM 1 then this is the first row of the result set.

ROWNUM is free to use conditional symbols like =, <,>, or a combination of them.

--
SELECT COUNT(*)
  FROM employee_table
 WHERE employee_name like 'Name-5%'
   AND rownum = 1
Result 
Count(*)
1
--
--
SELECT COUNT(*)
  FROM employee_table
 WHERE employee_name like 'Name-5%'
   AND rownum =< 5
Result 
Count(*)
5
--

In this example, take count as output, showing it as 5. If the table available more than 5 records that match the condition still returns the first 5 records. Here main advantage here is that there is no further scanning after selecting the matching 5 records from the table. So, the output will be fast compared to fetching all records from the table.

Do you know?

You are expecting records ordered by salary, and take the first 5 records using ROWNUM. But it is not like that. Oracle might pick the first 5 random records that are not ordered by SALARY. Therefore, if you are looking for a sorted dataset, use FETCH FIRST with ORDER BY

--
select * 
  from emp 
 where ROWNUM <= 5 
 order by salary desc;
--

But if you prefer sorting, then you have to modify the query as below. You must sort first and then take the first records from it.

--
select *
  from  
  ( select * 
    from emp 
    order by salary desc ) 
 where ROWNUM <= 5;
--

The FETCH FIRST Clause

 Oracle 12c or later, Oracle SQL limit rows by using the FETCH FIRST Clause. This is like a LIMIT clause in other databases. This also limits the number of records returned by the database. This is an example of fetching 5 records from the table.

--
SELECT * FROM employee_table WHERE employee_name like 'Name-%'
FETCH FIRST 5 ROWS ONLY.

Syntax:
This is the basic syntax for the FETCH FIRST clause is
FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } { ONLY | WITH TIES }
--

FETCH NEXT and OFFSET

This is another way of limiting the number of records. This FETCH NEXT is mostly combined with the OFFSET clause. OFFSET is used to set the starting point to fetch records. Then FETCH NEXT is used to take the required count of rows from the table.

--
SELECT *
FROM table
ORDER BY column
OFFSET 2 ROWS
FETCH NEXT 5 ROWS ONLY;
--

In this example OFFSET clause skips the first 2 records and fetches the next 5 records using the FETCH NEXT clause. Then return the query without fetching the remaining records. ORDER BY is optional. Also, OFFSET is not available, then Oracle considers OFFSET as 0 as the default.

If you have a lot of records in the table, this method is used to oracle SQL limit rows and paginate them. That helps to work with large tables and present data in a useful manner in the interface.

If OFFSET is not available, the default is 0, that records are returned starting with the first record. If FETCH is not specified, it means that the query has no limitation on the records to return.

Analytic Query ROW_NUMBER()

This is not straightforward, but still we are able to Oracle SQL limit rows.  ROW_NUMBER() is an Oracle SQL analytic function that returns the row number. By using the row number, we can limit the number of records that can be returned

--
SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY name) rnum
  FROM yourtable t
)
WHERE rnum BETWEEN 1 AND 5;
--

In this example inner query returns the row number, and from our query, we have added a condition to limit the records. 

LIMIT

LIMIT is not available to Oracle SQL limit rows, but the command exists in Oracle OLAP and Oracle NoSQL Database.  Oracle NoSQL Database SQL, this also specifies the maximum number of records to return in the table. Below is an example of it

--
SELECT * FROM table 
ORDER BY column 
LIMIT 5;
--

Oracle SQL row limit in standard Oracle SQL, you can use our earlier discussion on ROWNUM for simple row limiting or FETCH FIRST (with ORDER BY to make them ordered) in Oracle 12c and later.

SQL TOP PERCENT

This syntax of SQL Server/MS Access, therefore, I’m not going to explain everything about TOP, but you have example code below.

--
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
--

Note: As I explain in this article, not all database systems support the SELECT TOP keyword. MySQL works with the LIMIT clause to select a top number of rows, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

Key Differences rownum vs fetch first rows only

• ROWNUM is executed before ORDER BY, while FETCH FIRST ROWS ONLY is executed after ORDER BY. This is the most important feature and the difference.
• Performance: If you are no matter with order of dataset ROWNUM can offer performance advantages, but FETCH FIRST ROWS ONLY uses the Oracle analytic functions ROW_NUMBER(), which causes a performance problem than a ROWNUM. 
• FETCH FIRST ROWS ONLY is recommended after Oracle 12c and later versions to limit rows, especially if ordering is crucial. ROWNUM is still valid for simple row limiting without ordering.

Oracle SQL Developer limits rows

Use similar syntax in the above to filter records in SQL Developer as well. There are no changes with them in the SQL developer or PLSQL developer as well.

Conclusion

How to oracle SQL limit rows? 5 ways. No, it is not, right? You have 4 ways to do that. But it is important to limit the row on query because full table scans or if it is an index range scan query will take time to fetch all. But most of the time it is useless.

If large tables limit the top no of records, it helps to better use experience and improve the better performance on queries. Also, once validating query conditions are available for at least one record, you don’t want to fetch everything. A single record is enough. Therefore, use Oracle SQL to limit rows smartly.

Ref https://www.beekeeperstudio.io/blog/oracle-limit