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);

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);

## No comments:

## Post a Comment