How to find top three highest salary in emp table in oracle?

There are multiple way to find top three highest salary in emp table in oracle database.

Using count and distinct :-

  • The count() function is an aggregate function that returns the number of items in a group.
  • The distinct() function returns the number of unique values in a field for each GROUP BY result.
  • Here first we have created one emp table.
  • In that table there are 5 column id, name, age, address and salary.
  • Here first we fetch the emp data.
  • Three row is greater than distinct salary and then print order by salary in descending order.

        syntax of the COUNT() function :-  COUNT( [ALL | DISTINCT | * ] expression)

emp table :- select * from emp;

ID NAME AGE ADDRESS SALARY
1 Vinay 25 Delhi 1500
2 Pankaj 23 Mumbai 2000
3 Ravi 25 Chennai 6500
4 Kamal 27 Bhopal 8500
5 Mukesh 22 Hyderabad 4500
6 Indresh 24 Indore 10000

select * from emp a where 3 >= (select count(distinct salary) from emp b where a.salary <= b.salary) order by a.salary desc;

ID NAME AGE ADDRESS SALARY
6 Indresh 24 Indore 10000
4 Kamal 27 Bhopal 8500
3 Ravi 25 Chennai 6500

Using rownum and order by :-

  • Here first we fetch salary in descending order.
  • Then use this salary to print where rownum<=3 and order by salary.

select * from (select * from emp order by salary desc) where rownum <= 3 ;

ID NAME AGE ADDRESS SALARY
6 Indresh 24 Indore 10000
4 Kamal 27 Bhopal 8500
3 Ravi 25 Chennai 6500