Forum

Forums
SQL
Interview Questions...
Select the 3rd High...
 
Notifications
Clear all

Select the 3rd Highest Salary in SQL Using LIMIT

 
Interview Questions & Answers
Last Post by SQL Admin 1 year ago
2 Posts
2 Users
0 Reactions
3,204 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Feb 23, 2025 4:50 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

Introduction

Retrieving the Nth highest salary in SQL is a common interview question and a crucial skill for database professionals. In this guide, we’ll focus on how to fetch the 3rd highest salary using SQL, covering multiple approaches for different databases.

By the end of this post, you will:
✅ Understand how to use ORDER BY and LIMIT to fetch the 3rd highest salary.
✅ Learn an alternative method using subqueries to retrieve the result.
✅ Discover how to handle duplicate salaries effectively.
✅ Explore performance optimizations for large datasets.
✅ See how to use DENSE_RANK() for databases that support window functions.

Let’s dive in!

Understanding the Problem

Consider an employees table with the following data:

idnamesalary
1Alice5000
2Bob7000
3Carol6000
4Dave8000
5Eve7500

Goal: Fetch the 3rd highest salary from this table.

SQL Query Using LIMIT and OFFSET

The easiest way to find the 3rd highest salary is by using ORDER BY and LIMIT:

 
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

How It Works:

🔹 ORDER BY salary DESC → Sorts salaries in descending order (highest to lowest).
🔹 LIMIT 1 → Fetches only one record (the 3rd highest salary).
🔹 OFFSET 2 → Skips the top 2 highest salaries, fetching the 3rd highest.

Example Output:

salary
------
6000

Alternative Method: Using a Subquery

For databases where LIMIT isn’t available, we can use a subquery:

 
SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 3) AS subquery;

Explanation:

✅ The inner query selects the top 3 distinct salaries.
✅ The outer query retrieves the minimum salary from those, which is the 3rd highest salary.

Handling Duplicate Salaries

If the salary column contains duplicates, we must ensure unique salaries are considered. The use of DISTINCT in our queries ensures that duplicate salaries don’t interfere with our results.

For example, if two employees share the same 3rd highest salary, our method still correctly retrieves it.

Performance Considerations

💡 Optimizing the Query for Large Datasets:
🔹 Indexing: Adding an index on the salary column improves performance:

 
CREATE INDEX idx_salary ON employees(salary);

🔹 Avoiding Full Table Scans: If your database supports window functions, they offer a more optimized approach.

Alternative Approach: Using DENSE_RANK()

For databases that support window functions, we can use DENSE_RANK() to achieve the same result:

 
SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) ranked WHERE rnk = 3;

How It Works:

🔹 DENSE_RANK() OVER (ORDER BY salary DESC) → Assigns a rank to each unique salary.
🔹 The outer query filters results where rnk = 3 (the 3rd highest salary).

💡 Why Use DENSE_RANK()?

  • It is more efficient than LIMIT for large datasets.
  • It ensures correct ranking even if duplicate salaries exist.

Conclusion

Fetching the 3rd highest salary in SQL can be achieved using multiple approaches:
✅ LIMIT with OFFSET – The simplest method for databases that support it.
✅ Subqueries – A great alternative for databases that don’t support LIMIT.
✅ DENSE_RANK() – The best approach for large datasets and when duplicate salaries exist.

Each method has its own advantages, and choosing the right one depends on your database system and performance requirements.

💬 Have questions? Discuss SQL problems in our SQL Community Forum!

🔗 Join the SQL Queries Community: SQL Forum

For more advanced SQL techniques, check out this MySQL Documentation on LIMIT:
🔗 MySQL LIMIT Documentation


This topic was modified 11 months ago by SQL Admin
Topic Tags
3rd highest salary in SQL SQL LIMIT OFFSET SQL interview questions SQL ranking functions best SQL queries for int
1 Reply
Posts: 134
 SQL Admin
Admin
Topic starter
Feb 27, 2025 6:53 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

1️⃣ How to Find Nth Highest Salary in SQL? | Multiple Ways Explained

Description:

When working with SQL, interviewers often ask about retrieving the Nth highest salary rather than just the 3rd highest. Let’s explore multiple ways to do this efficiently across different databases.

✅ Using LIMIT with OFFSET (Best for MySQL, PostgreSQL)

 
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1;

👉 Explanation: It skips (N-1) salaries and returns the Nth highest.

✅ Using Subquery (Works in Almost All Databases)

 
SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT N) AS subquery;

👉 Explanation: The subquery fetches the top N salaries, and the outer query picks the minimum (Nth highest).

✅ Using DENSE_RANK() (Best for Large Datasets with Duplicate Salaries)

 
SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) ranked WHERE rnk = N;

👉 Explanation: It assigns a rank to each salary and selects the Nth ranked value.

📌 Key Takeaways:

  • LIMIT with OFFSET is simple but inefficient for large datasets.
  • DENSE_RANK() is the best for databases that support window functions.
  • Subquery with MIN() is a universal solution when window functions are not supported.

🔗 Related Discussion: Join the SQL Community for More SQL Queries!
🔗 External Reference: MySQL Documentation on ORDER BY

📌 Keywords: Nth highest salary SQL, SQL interview question, SQL ranking functions, SQL ORDER BY, SQL query for highest salary

2️⃣ Find the 2nd, 3rd, and 4th Highest Salary in SQL Without Using LIMIT

Description:

In databases that don’t support LIMIT, you can still find the Nth highest salary using other techniques. Let’s explore some alternatives.

✅ Using Correlated Subquery (Works Everywhere, But Slow on Large Data)

 
SELECT salary FROM employees e1 WHERE 3 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary >= e1.salary);

👉 How It Works?

  • The inner query counts how many salaries are greater than or equal to e1.salary.
  • If the count equals N, it selects that salary.

✅ Using CTE with ROW_NUMBER() (Best for SQL Server, PostgreSQL, Oracle)

 
WITH SalaryRank AS ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees ) SELECT salary FROM SalaryRank WHERE rank = 3;

👉 How It Works?

  • It assigns a row number to each salary.
  • The outer query selects the Nth salary where rank = N.

📌 Key Takeaways:

  • Correlated subqueries are universally supported but slow for large datasets.
  • CTE with ROW_NUMBER() is better for performance if your SQL version supports it.

🔗 Related Discussion: SQL Queries Forum
🔗 External Reference: PostgreSQL Window Functions

📌 Keywords: SQL highest salary, SQL without LIMIT, SQL ranking methods, SQL CTE, SQL interview queries

3️⃣ What’s the Difference Between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Description:

When working with SQL ranking functions, developers often get confused between ROW_NUMBER(), RANK(), and DENSE_RANK(). Let’s break it down with examples.

FunctionDescriptionHandles Duplicates?Example Output
ROW_NUMBER()Assigns a unique row number to each row, even if duplicates exist❌ No1, 2, 3, 4, 5
RANK()Assigns a rank, but skips numbers after duplicates✅ Yes1, 2, 2, 4, 5
DENSE_RANK()Assigns a rank without skipping numbers after duplicates✅ Yes1, 2, 2, 3, 4

✅ Example SQL Query:

 
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, RANK() OVER (ORDER BY salary DESC) AS rank_num, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num FROM employees;

👉 Explanation:

  • ROW_NUMBER() assigns unique numbers even for duplicate values.
  • RANK() assigns same ranks to duplicates but skips numbers.
  • DENSE_RANK() assigns same ranks but does not skip numbers.

📌 Key Takeaways:

  • Use ROW_NUMBER() when you need unique IDs per row.
  • Use RANK() when you need ranking but with gaps.
  • Use DENSE_RANK() when you need ranking without gaps.

This post was modified 11 months ago by SQL Admin
Reply

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 LIMIT OFFSET (1) , SQL interview questions (4) , SQL ranking functions (3) , best SQL queries for int (1) ,
Share: