Nth Highest Salary SQL Challenge - Oracle Solution

1. Introduction

This blog post explores how to determine the nth highest salary in an Oracle database. Such queries are common in data analysis and can be approached in various ways in SQL.

Problem

The goal is to create an Oracle SQL query to find the nth highest salary from the Employee table. If the nth highest salary does not exist (for instance, if there are fewer employees than n), the query should return null.

Database Table:

Employee Table:

id	salary
1	100
2	200
3	300

3. Oracle Solution

SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM Employee
) WHERE rank = N;

Output:

Output for n = 2:

getNthHighestSalary(2)
200

Output for n = 2 with only one employee:

getNthHighestSalary(2)
null

Explanation:

1. The query uses the DENSE_RANK() window function in Oracle to rank salaries in descending order.

2. It assigns a unique rank to each distinct salary.

3. The query then selects the salary where its rank is equal to the specified n value.

4. If the nth highest salary does not exist, it returns null.