Oracle Select Top 1 Record

Oracle Select Top 1 Record

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

This tutorial shows, how to use Oracle Select statement to get the top 1 record from a table.

In my previous tutorial, I have given the example to get the top 10 records from a table in Oracle. The method is the same whether you want to get the top 10, top 1, or top any number of records. Here I am showing the examples again:

Oracle Select Statement to Get Top 1 Record

The following Select statement will get the highest salary from the EMP table:

select empno, ename, job, mgr, sal from (
  select * from emp order by sal desc)
  where rownum = 1

Output

EMPNOENAMEJOBMGRSAL
7839KINGPRESIDENT-5000

Using Row_Number() Function to Get the Top 1 Record

Below SQL query will get the highest salary using the Row_number() analytical function:

select empno, ename, job, mgr, sal from (
   select empno, ename, job, mgr, sal, 
   row_number() over (order by sal desc) rn 
   from emp order by sal desc)
   where rn = 1

The output will be the same as above.

Using Fetch Clause to Get The Top 1 Record in Oracle

If you are using Oracle 12c or above, then it will be the simplest to get the top N records. Below is an example:

select empno, ename, job, mgr, sal
  from emp order by sal desc
  fetch next 1 rows only