Forum

Forums
SQL
Interview Questions...
Write an SQL Query ...
 
Share:
Notifications
Clear all

Write an SQL Query to Find the Nth Highest Salary in a Table

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:29 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

How can you write an SQL query to find the Nth highest salary in a table? Assume you have an employees table with columns employee_id, employee_name, and salary.

Answer:

Finding the Nth highest salary is a common SQL problem, which can be solved using window functions like DENSE_RANK() or a subquery with LIMIT and OFFSET. Below, we cover each approach.

Solution 1: Using DENSE_RANK()

Assume the employees table structure is as follows:

employee_id employee_name salary
1 Alice 90000
2 Bob 85000
3 Charlie 75000
4 David 95000
5 Eve 85000

To find the Nth highest salary, say the 3rd highest, we can use the DENSE_RANK() function.

SQL Query:

sql
 
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) AS ranked_salaries
WHERE salary_rank = 3;

Explanation:

  1. DENSE_RANK() Function: This function ranks the salaries in descending order. DENSE_RANK() assigns the same rank to duplicate salaries, so if two employees share the same salary, they get the same rank.
  2. Filter by Rank: The WHERE salary_rank = 3 filter retrieves only the salary in the 3rd highest position.

Expected Output:

Based on the example data, the output for the 3rd highest salary would be:

salary
75000

Solution 2: Using OFFSET and LIMIT (MySQL-specific)

In databases like MySQL, you can achieve the same result using OFFSET and LIMIT to skip a specified number of rows.

SQL Query for Nth Highest Salary (3rd highest example):

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

Explanation:

  1. ORDER BY salary DESC: The salaries are ordered in descending order so the highest salaries come first.
  2. LIMIT 1 OFFSET 2: OFFSET 2 skips the first two highest salaries, and LIMIT 1 retrieves the next salary (the 3rd highest).

This solution is useful when working with databases that support LIMIT and OFFSET (like MySQL), but it does not account for duplicate salaries as accurately as DENSE_RANK().

Expected Output for Solution 2:

The output will be the same, with the 3rd highest salary being:

salary
75000

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: