In most cases, databases comprise of thousands to millions of records in a table. It can be daunting to manually review all the records when performing queries.
Hence, you will need to use various clauses in SQL, such as conditional statements, pattern-matching functions, operators, etc. Even then, you can end up with numerous records to sort through.
We can use the limit functionality in SQL and other SQL-Like dialects to further limit the number of rows returned from a given query.
This post will discuss a short format for limiting the number of records returned from a given Oracle query. Remember that there is no LIMIT clause in the Oracle database, as you will find in other databases such as MySQL, PostgreSQL, etc.
Oracle ROWNUM
For us to implement a limit-like clause, we are going to use the rownum function in SQL. This function returns the number of rows in a given result.
Let us explore how we can use this function.
Example Usage
Suppose we have a table with employee information, as shown in the image below:
Problem
Suppose we wish to fetch the first five columns from the table above and ignore the rest?
Solution
We can use a subquery as shown in the snippet below:
from (select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from EMPLOYEES order by EMPLOYEE_ID asc)
where ROWNUM <= 5;
In the query above, we define a subquery using the from clause. Then, the subquery fetches the target columns and sorts the rows in ascending order based on the employee_id.
Finally, we define a conditional statement that ensures the returned number of rows is less than or equal to 5. This forces the number of records from the query to stop at five.
An example output is as shown:
Keep in mind that using the subquery ensures that the rows are sorted in ascending or descending order. So, for example, if you wish to fetch five random rows, run:
from EMPLOYEES
where ROWNUM <= 5;
Output:
From the output above, you may not notice the difference. However, running the query without an order by clause in unsorted data will result in a random set of values.
Conclusion
This short but valuable tutorial explored implementing the LIMIT clause in the Oracle database using the ROWNUM value.
