Wednesday, 16 December 2015

Display second highest salary from the table

Requirement: I have an EMP table with the below data:

Now I want to get the Employee detail who is receiving the second highest salary that is an employee whose salary = 17000

Solution: For solution to the above requirement follow the write the query as shown below:

Query: 
SELECT * FROM emp E1 WHERE
(SELECT COUNT(DISTINCT(E2.SALARY)) FROM emp E2 WHERE E2.SALARY >= E1.SALARY)=2;

For each salary in EMP  E1, find the number of salaries greater than or equal to the current salary row of E1.
If the count is 2, that means that there is one record greater than the current record and one is itself. The record itself then obviously becomes the second highest salary.

For example, assume EMP has 3 records with salary:
20
50
100

If you run the query,
The first record in E1 is 20, it checks in E2 for the count of records > = 20, it will return 3. So, no match is found.
The second record in E1 is 50, it checks in E2 for the count of records > = 50, it will return 2. So MATCHED!.
It will return 50 as this is the second highest i.e. only one salary greater than itself.

Output:

Hence, the solution to our requirement. 


 
Thanks & Regards,
Susanto Paul