How to use DENSE_RANK() function In SQL
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 :
|
Id |
Name |
Group_Id |
Salary |
|---|---|---|---|
|
1 |
Yash |
1 |
25000 |
|
5 |
Keval |
2 |
30000 |
|
7 |
Jenil |
3 |
15000 |
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.
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.
Contact Us