Query to get 3 minimum salaries in oracle.

There are multiple way to find Query to get 3 minimum salaries 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 a.salary <= b.salary 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;

IDNAMEAGEADDRESSSALARY
5Mukesh22Hyderabad4500
2Pankaj23Mumbai2000
1Vinay25Delhi1500

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 in descending order.

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

IDNAMEAGEADDRESSSALARY
5Mukesh22Hyderabad4500
2Pankaj23Mumbai2000
1Vinay25Delhi1500