Saturday 23 November 2013

Retrieving Minimum Salary from the database without using Min function

Bismillahir Rahmanir Raheem

How to retrieve Minimum salary without using Min function

As we have retrieved the maximum salary the concept is same Use self join.

name                                    salary
A                                         4000              
B                                         3000    
C                                         5000
D                                         2500                                        
E                                         1000
F                                         1500

Select a.salary
from account a,account b
where a.salary > b.salary;

Now the  minimum salary is the only one which is not greater than any other salary
All other salary is greater to at least  minimum salary.

This will give result containing all salaries except minimum salary

Now we will write an outer query to select salary that is not inn salaries returned by inner query

Select salary as Minimum_Salary
from account
where salary not in (Select a.salary
                              from account a,account b
                              where a.salary > b.salary);

This will give you the required result

Minimum_Salary
-----------------
1000

Remember one thing Corelated queries is different than independent qurey.



No comments:

Post a Comment