List the Second Highest Salary By Department

Listing the second-highest salary by department is a common task that explains the use of advanced PostgreSQL techniques. It offers several functions and methods to achieve this.

In this article, we will explore three approaches that help us to List the Second Highest Salary By Department with the help of examples and so on.

Listing the second-highest salary by the department is a practical example that showcases the use of advanced SQL techniques. PostgreSQL is a powerful open-source relational database that offers below functions to List the Second Highest Salary By Department are as follows:

1. Using Subquery
2. Using DENSE_RANK() function
   
3. Using Common Table Expressions

To understand 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 table called Test as follows:

CREATE TABLE "Test" (
    "Id" integer NOT NULL,
    "Group_Id" integer NOT NULL,
    "Salary" integer NOT NULL,
    "Name" character varying NOT NULL,
    CONSTRAINT "Test_pkey" PRIMARY KEY ("Id")
);
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (1, 1, 25000, 'Yash');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (2, 1, 30000, 'Darshan');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (3, 1, 20000, 'Parth');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (4, 2, 35000, 'Vraj');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (5, 2, 30000, 'Keval');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (6, 2, 20000, 'Abhi');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (7, 3, 15000, 'Jenil');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (8, 3, 10000, 'Akshay');
INSERT INTO "Test" ("Id", "Group_Id", "Salary", "Name") VALUES (9, 3, 20000, 'Vivek');

We have successfully inserted data in our table. Now our Table will look as below.

By using the Joins, we can use subquery for finding the nth highest salary. In our approach, we will simply find all the rows which have the salary lower than the maximum salary. From that result we will find the highest salary which will be our desired result. Here is the query for this approach.

SELECT t."Group_Id", MAX(t."Salary") AS Salary
FROM "Test" t
WHERE t."Salary" < (
    SELECT MAX("Salary")
    FROM "Test" t2
    WHERE t2."Group_Id" = t."Group_Id"
)
GROUP BY t."Group_Id";

Output:

Explanation:

We have simply find the highest salary in each group and fetch all the records which have less salary than highest salary. In other words we have removed the records which have highest salary. Now from the remaining records highest salary will be the 2nd highest salary of the overall records.

What is DENSE_RANK()?

DENSE_RANK() is used to assign a rank to each record of each section in our result set. For each partition, DENSE_RANK() returns same rank for the rows having the same value. We will use DENSE_RANK() function to find the second highest salary by department.

In other words, DENSE_RANK() function can be used to find nth highest salary. Let’s see how to find 2nd highest salary using DENSE_RANK() function.

SELECT "Id", "Name", "Group_Id", "Salary"
FROM (
    SELECT *,
           DENSE_RANK() OVER (PARTITION BY "Group_Id" ORDER BY "Salary" DESC) AS rnk
    FROM "Test"
) AS t
WHERE rnk = 2;

Output :

Explanation: Here we also have used subquery but in subquery we have used DENSE_RANK() function to give sequence number to each row of the partition. After that we have fetched 2nd record of each partition which will be 2nd highest salary of each Group.

We have done partition on Group_Id and ordered the results in Descending order because we want 2nd highest salary. we have applied where condition in which we have simply asked to give all the records which have 2nd rank in all the partitions. If we apply on rank 1 then it will return highest salary in all the partitions.

By using Common Table Expression(CTE) we can create a temporary table and will use ROW_NUMBER() function. Result table after applying ROW_NUMBER() function will be temporary store. It will not reflet in the database. Here we are assuming that no two salary will be the same in same partition.

WITH temp_table AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY "Group_Id" ORDER BY "Salary" DESC) AS rownum
    FROM "Test"
)
SELECT "Id", "Name", "Group_Id", "Salary"
FROM temp_table
WHERE rownum = 2;

Output:

Explanation:

Here we have used Common Table Expression(CTE) . Using which we have created temp_table and applied ROW_NUMBER() on the Salary and also done partition on Group_Id. Then we have fetched second row of each partition which will be 2nd highest salary of each Group.

We have created a temporary table using Common Table Expression (CTE). Then we have applied partition over Group_Id and set the salary is descending order. Then using ROW_NUMBER() we have given a sequence to each partition. Since we assume that no two person have same salary in the same partition, 2nd highest salary will have row number 2 in each partition.

By implementing SQL queries efficiently, this task becomes manageable and provides valuable insights into departmental salary structures. Here we have retrieved our desired result using multiple methods like self join and subqueries and other is DENSE_RANK() function, Common Table Expression(CTE). DENSE_RANK() function can be used for finding nth highest salary. But for finding the nth highest salary using subquery, it will get tough when the value of n will increase. Using Common Table Expression we can create temporary table and use window functions such as ROW_NUMBER().