Here we will see the Oracle Select statement examples to get the top 10 records from a table.
Oracle Select Top 10 Records Using Rownum
You can use the rownum. As soon as Oracle receives the result, it modifies it by adding the rownum column.
After the result has been returned, you need to apply filters to it, and for this, you'll need to use a subquery. Below is an example to get top 10 salaries:
select empno, ename, job, mgr, sal from ( select * from emp order by sal) where rownum <= 10
Oracle SQL Query to Get Top 10 Using Fetch Clause
If you are using Oracle 12c or a later version, you can use the
FETCH NEXT N ROWS ONLY statement. Below is an example:
select empno, ename, job, mgr, sal from emp order by sal fetch next 10 rows only
Use Row_Number() Analytical Function To Get Top 10 Records in Oracle
ROWNUM is applied before
ORDER BY, you get an apparently random set. As a result, your query sorts the first ten rows. To find the top ten salaries, use the
row_number() analytic function in a subquery and then filter.
select empno, ename, job, mgr, sal from ( select empno, ename, job, mgr, sal, row_number() over (order by sal) rn from emp order by sal) where rn <= 10
The result will be the same as above.