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 |
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.
Comments
Post a Comment