PL/SQL Query to List the Second Highest Salary By Department

Fetching the highest salary or nth lowest salary can be considered a common task when we are dealing with some real case scenarios. We often face such types of scenarios when there is sales performance analysis, merit-based promotions, salary benchmarking, etc. These are a few use cases to fetch nth highest or lowest salary. It can provide us an insight into whether there is a significant gap between the highest-paid employee and other employees within the same department.

In this article, we are going to discuss “how to calculate the second highest salary from each department ” by understanding various approaches along with that we will see various examples with their clear and concise examples.

PL/SQL Query to List the Second Highest Salary By Department

• The task is to find the second-highest salary for each department in PL/SQL. One approach is to first find the highest salary for each department and then find the second highest salary excluding the highest one.
• Another approach involves using the ROW_NUMBER() function to fetch the Row With The Second Highest Salary From Each Department.

Let’s set up an Environment:

To understand PL/SQL Query to List the Second Highest Salary By Department we need a table on which we will perform various operations and queries. Here we will consider a collection called geeksforgeeks which contains information such as id, name, salary, and department as Columns.

Table – geeksforgeeks

Fetching The Row With The Second Highest Salary From The Entire Department

• In this example, we are going to fetch the second highest salary from the entire department. We will first find the highest salary among the departments. Then we will search from the rows which contains less value in the salary column than the highest salary.
• At last we will find the highest salary among those columns. Therefore, finally getting the second highest salary. Lets see the query for more clear understanding.

Query:

DECLARE
    v_secondHighest NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('ID  | Name  | Department  | Salary ');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------');
    SELECT MAX(salary)
    INTO v_secondHighest
    FROM geeksforgeeks
    WHERE salary < (SELECT MAX(salary) FROM geeksforgeeks);

    FOR i IN (SELECT id, name, department, salary FROM geeksforgeeks where salary = v_secondHighest) LOOP
        DBMS_OUTPUT.PUT_LINE(i.id || ' | ' || i.name || ' |   ' || i.department || '        | ' || i.salary);
    END LOOP;
END;

Output:

Second highest Salary From Entire Department

Explanation: In the above query, we first find the highest salary among the entire department. Then we will search through all the rows where salary is lower than the highest salary, and find the highest salary among those rows. This will eventually provide us the second highest salary from entire department. Then at the end, we will loop through each row in the table. and match the salary column values with the value obtain in the second highest salary variable. If it matches, then we will display row’s complete details. We can refer to the image for more clear understanding.

Fetching The Row With The Second Highest Salary From Each Department

• In this example, we will fetch the second highest salary from each department. There are two department in our table, they are ‘IT’ and ‘HR’. Our main goal will be to find the second highest salary from these two departments. We will use ROW_NUMBER() function to get the row id’s of the rows.
• We will sort the data of our table with respect to the salary and partition with the department. Through row id’s , we will get the second row, which is eventually denoting the second highest salary of the each department. Lets have a look to the query for more clear understanding.

Query:

DECLARE
    v_department VARCHAR2(50);
    v_salary NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('ID  | Name  | Department  | Salary ');
    FOR i IN (SELECT DISTINCT department FROM geeksforgeeks) LOOP
        SELECT department, salary INTO v_department, v_salary
        FROM (
            SELECT department, salary, 
                   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rowNumber
            FROM geeksforgeeks
            WHERE department = i.department
        )
        WHERE rowNumber = 2;
        DBMS_OUTPUT.PUT_LINE('-----------------------------------');
        FOR i IN (SELECT id, name, department, salary FROM geeksforgeeks where salary = v_salary and department = v_department) LOOP
            DBMS_OUTPUT.PUT_LINE(i.id || ' | ' || i.name || ' |   ' || i.department || '        | ' || i.salary);
        END LOOP;
    END LOOP;
END;

Output:

Second highest Salary From Each Department

Explanation: In the above image, we can clearly see that both the departments second highest salaries are displayed. Highest salaries of HR and IT departments are 63000 and 65000. Therefore if we change the rowNumber to 1, then we will get the highest salary. Same if ye do it as 3, then third highest salary and so on. Through this code snippet, we can calculate the nth highest salary.

Conclusion

Overall, calculating nth highest or nth lowest salaries can play a significant role in a company’s budget making, performance analysis, merit based promotions etc. We have seen how we can calculate the second highest from the entire department as well as how we can calculate the second highest salaries from each department. We have also seen a general code for calculating the nth highest salary. We have covered all the basic syntax of the examples with clear and concise explanations. Now you can write queries related to it and can get the desired output.