Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.
Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries. Co-related queries are very interesting stuff as it gives you option to process data row wise. But at the same time it has a big drawback. Row wise processing causes bottle neck for the performance of the query.
Query to get the list of employees getting 2nd highest salary (Set the value of @SalaryPosition to get Nth highest salary):
Well the query looks simple but understanding it is little bit complex. Let’s explore the process behind it.
I have taken the variable @SalaryPosition in order to make it standardize while checking salaries in more than one level.
Before going further, let me tell you the concept behind the co-related query.
Co-Related Query: In short, it’s a type of nested sub query where the inner query is referenced to some value of the outer query. And the inner query is executed for each record in the outer query making it slowL. More appropriate example of a Co-Related query is the IN () operator.
In our query, to get the 2nd highest salary, works in the same way too. Let’s dive a little deeper.
Sample data used in employee table:
Run the below query to create the table in your database and to insert data into it.
So we have taken two aliases for the employee table i.e. E1 and E2.
Now let’s see how to get the employee names getting 2nd highest salary.
Here E1 is the outer query and E2 is the inner query.
Corollary: Let’s take the first record in the table E1 where the salary is 5000 and assume it as the second highest salary. But how will you know if it is actually the second highest salary or not. If 5000 is the second highest salary, then the distinct count of all the salaries from table E2 which are greater than or equal to 5000 must be 2.
The first record from E1 (salary = 5000) will be compared to all the records in E2 (salary).
|Table E1 (considering the first record only)||Table E2 (distinct salary)||Distinct count where salary is >= 5000|
Hence 5000 is not the highest salary as we have 4 more salaries greater than equal to 5000. But it is concluded that 5000 is at the fourth position.
Now let’s take the second salary from table E1 and do the same comparison as in the earlier case.
The second salary is 7500 and as per rule the count of distinct salaries greater than equal to 7500 must be 2.
|Table E1 (considering the second record only)||Table E2 (distinct salary)||Distinct count where salary is >= 5000|
Here in this case our condition in the where clause of outer query is satisfying. Let’s dissect the query for this particular case.
Step2: (where the salary is 7500, inner query will return 2 as only two salaries are there in table E2 that are greater than equal to 7500.)
Step3: (Now 2 = 2, the outer query condition satisfies, hence it will return the corresponding record in the result set.
Once it returns the record 101, Abhishek, 7500 it will iterate through other records in table E1 to check if any other record is satisfying the same condition. In our example two records satisfies the condition “where 2=2”, hence both the records returned to the result set.
Finally we got all the employee information with second highest salary.
If you want to check for highest salary or second highest salary, then just change the value of @SalaryPosition accordingly.
Other ways to get list of employees getting 2nd highest salary in the organization are:
[ Copy to Clipboard ] | [ View Source ]
This article has been taken from SQL Lion, one of the best place for SQL