In case second highest value is missing then use the highest value of salary from employee table-CodePudding

I have an employee table as below. As you can see that second highest salary is 200 Incase the second highest salary is missing then there will be only one row as shown at last . In this case the query should fetch only 100 I have written query as but it is not working. Please help! Thanks

select salary "SecondHighestSalary" from( (select id,salary,rank() over(order by salary desc) rnk from employee2) )a where (rnk) in coalesce(2,1)

I have also tried the following but it is fetching 2 rows but i need only 1

CodePudding user response：

It sounds like you'd want something like

with ranked_emp as (
  select e.*, 
         rank() over (order by e.sal desc) rnk, 
         count(*) over () cnt
    from employee2 e
)
select salary "SecondHighestSalary"
  from ranked_emp
 where (rnk = 2 and cnt > 1)
    or (rnk = 1 and cnt = 1)

Note that I'm still using rank since you're using that in your approach and you don't specify how you want to handle ties. If there are two employees with the same top salary, rank will assign both a rnk of 1 and no employee would have a rnk of 2 so the query wouldn't return any data. dense_rank would ensure that there was at least one employee with a rnk of 2 if there were employees with at least 2 different salaries. If there are two employees with the same top salary, row_number would arbitrarily assign one the rnk of 2. The query I posted isn't trying to handle those duplicate situations because you haven't outlined exactly how you'd want it to behave in each instance.

CodePudding user response：

If you are in Oracle 12.2 or higher, you can try:

select distinct id,
 nvl
(
 nth_value(salary, 2) from first 
 over(partition by id 
      order by salary desc 
      range between unbounded preceding and unbounded 
      following), 
 salary
) second_max_salary
 from employee2