CODEWITHSUNDEEP
sqloracle-database
Learner
Learner

Reputation: 1

Rank NULL Value as well while using Rank in Oracle SQL

I am a newbie and learning SQL. While practising SQL on a practise platform, I came across the below question : Write a SQL query to get the second highest salary from the Employee table.

|Id  | Salary 
| 1  | 100   
| 2  | 200 
| 3  | 300

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

| SecondHighestSalary | | 200 |

I intend to use Oracle Analytical function to write the query. The query I have written is as below :

select Salary as SecondHighestSalary 
from (SELECT salary, dense_rank() over(order by salary desc)  myrank 
FROM Employee)
where myrank =2;

My query is not able to handle the NULL values test case and throws me error. Can anyone answer my question?

Upvotes: 0

Views: 620

Answers (2)

Hetal Rachh
Hetal Rachh

Reputation: 1543

Below query will work as well. Both min(Salary) and max(Salary) will work.

SELECT min(Salary) as "SecondHighestSalary" FROM (
    SELECT Salary, dense_rank() over(ORDER BY Salary DESC) as dense_rank
    from Employee
    ) WHERE dense_rank=2;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You query is returning no rows when there is no second highest value. If you want a single row with NULL, you need to make that happen. A simple method would be an aggregation function:

select max(Salary) as SecondHighestSalary 
from (SELECT salary, dense_rank() over(order by salary desc) as myrank 
      from Employee
     ) e
where myrank = 2;

Upvotes: 2

Related Questions