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 w3wiki) 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 w3wiki
            WHERE department = i.department
        )
        WHERE rowNumber = 2;
        DBMS_OUTPUT.PUT_LINE('-----------------------------------');
        FOR i IN (SELECT id, name, department, salary FROM w3wiki 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:

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.

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.