Correlated SubQuery -The best way irrespective of database you are working with

Bismillahir Rahmanir Raheem

Now I am going to talk about the best way to deal with Queries Like finding Second max or min or any other Salary position and the best thing about this approach is that it is irrespective of database.
So it will work with MySQL,Oracle,MS SQL and any other that you probably know.

this is our table account

Name                           Salary
A                                  1000
B                                   500
C                                   800
D                                  1500
E                                  2000
F                                  3000

Before this we were using independent subqueries but in this approach we will use Correlated SubQuery.

So what is the difference between independent subquery and correlated subquery.
Independent subquery can execute even if the outer query is not present
But Correlated subquery will only execute when there is an outer query present .
So Correlated Subquery is only meaningful when there is an outer query.


So How can we find Maximum salary using Correlated subquery

How many salary is higher than Maximum salary ?
Answer : 0

Here is the solution query

Select salary
from account x
where 0=( select count(*)
                 from account y
                 where y.salary > x.salary);

So what about finding Minimum salary
How many salary is higher than Minimum Salary ?
Answer : count(*)-1

So first find number of rows inn the database using

Select count(*) from account;

So lets calculate second Highest salary
How many salary is higher than Second Highest Salary ?
Answer : 1

Select salary
from account x
where 1=( select count(*)
                 from account y
                 where y.salary > x.salary);