Oracle Select Top 10 Records

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

Output

Use Row_Number() Analytical Function To Get Top 10 Records in Oracle

Because 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

Output

The result will be the same as above.