Saturday 23 November 2013

Retrieving Maximum salary without using Max function in SQL

Bismillahir Rahmanir Raheem

Today We are going to find the maximum salary from the database
without using Max function.

So how to do that , we will use Self Join to do that.

Say this is our table Account

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

Note one thing that maximum salary is the only one that is not less than any other salary in the database
All other salaries are less than maximum salary and other.
Like 3000 is less than 4000 and 5000
        1000 is less than 1500,2500,3000,4000,5000
        Similarly for other salaries But 5000 is not less than any other salarie

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

This will give list of salary having all salary values except the Maximum Salary which is 5000 in this case.

Now we are just one step behind our answer , All we have to do is to nest this query in an outer query
The Outer query will say give me salary that is not in the result of the inner query

select salary as Maximum_Salary
from account
where salary not in ( Select a.salary
                               from account a,account b
                               where a.salary < b.salary);

Maximum_Salary
----------------
5000

This is your result.

No comments:

Post a Comment