Forum

Forums
SQL
Interview Questions...
Find the 3rd Highes...
 
Share:
Notifications
Clear all

Find the 3rd Highest Salary in SQL Using RANK

 
    Last Post
RSS

Posts: 97
 SQL Admin
Admin
Topic starter
Jan 10, 2025 5:14 am
(@sql-admin)
Estimable Member
Joined: 5 years ago

When working with databases, retrieving specific records based on rankings is a common requirement. One such scenario is finding the 3rd highest salary in a company database. SQL provides powerful tools like the RANK() function to accomplish this task efficiently. In this post, we’ll dive deep into how to use the RANK() function to solve this query. We’ll also explore alternative approaches, making it easier for you to handle similar tasks in the future.

Understanding the RANK() Function

The RANK() function is a window function in SQL used to assign a rank to each row within a partition of a dataset. The ranks are assigned based on the order specified in the ORDER BY clause. The key characteristic of RANK() is that it assigns the same rank to rows with identical values and skips ranks accordingly.

Syntax:

sql
 
RANK() OVER (PARTITION BY column_name ORDER BY column_name ASC|DESC)
  • PARTITION BY: Divides the dataset into groups. Optional for our use case.
  • ORDER BY: Specifies the order in which the ranks are assigned.

Step-by-Step Query to Find the 3rd Highest Salary

Sample Employee Table

Let’s consider the following Employee table:

EmployeeID Name Salary
1 Alice 70000
2 Bob 80000
3 Charlie 90000
4 David 80000
5 Eva 70000
6 Frank 60000

SQL Query:

Here’s how to use the RANK() function to find the 3rd highest salary:

sql
 
SELECT Salary
FROM (
SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS rnk
FROM Employee
) RankedSalaries
WHERE rnk = 3;

Explanation:

  1. Subquery:
    • The subquery calculates the rank for each salary in descending order.
    • Duplicate salaries receive the same rank.
  2. Outer Query:
    • Filters the rows where the rank is exactly 3.

Query Output:

For the above Employee table, the result is:

Salary
70000

Why Use RANK()?

  • Handles Ties Gracefully: The RANK() function assigns the same rank to duplicate values, making it ideal for datasets with non-unique salaries.
  • Flexible Sorting: Easily customize the ranking criteria using the ORDER BY clause.

Alternative Approaches

Using DISTINCT and LIMIT (or OFFSET in SQL Server)

This method works for databases where window functions aren’t supported:

sql
 
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 2;

Explanation:

  • DISTINCT ensures duplicate salaries are removed.
  • ORDER BY arranges salaries in descending order.
  • OFFSET skips the top two salaries, leaving the 3rd highest.

Using DENSE_RANK() Function

The DENSE_RANK() function is similar to RANK(), but it doesn’t skip ranks for duplicate values. For example:

sql
 
SELECT Salary
FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS drnk
FROM Employee
) DenseRankedSalaries
WHERE drnk = 3;

Using Subqueries

For a traditional approach without window functions:

sql
 
SELECT MAX(Salary)
FROM Employee
WHERE Salary < (
SELECT MAX(Salary)
FROM Employee
WHERE Salary < (
SELECT MAX(Salary)
FROM Employee
)
);

Explanation:

  • The innermost query retrieves the highest salary.
  • The middle query retrieves the second-highest salary.
  • The outer query retrieves the third-highest salary.

Conclusion

The RANK() function is a versatile tool for ranking and filtering data. It’s particularly useful in scenarios where duplicates exist, and precise rankings are needed. While alternative methods like subqueries and DENSE_RANK() are viable, RANK() remains the most efficient for modern databases.

To engage with more SQL enthusiasts and share your queries, join our SQL community forum. For additional insights into SQL window functions, refer to this comprehensive guide.

This topic was modified 3 months ago by SQL Admin
Topic Tags
3rd highest salary in SQL SQL rank function exampl find highest salary in S SQL window functions tut SQL RANK vs DENSE_RANK

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  3rd highest salary in SQL (2) , SQL rank function exampl (1) , find highest salary in S (1) , SQL window functions tut (1) , SQL RANK vs DENSE_RANK (1) ,
Share: