dense_rank() and max, which one is better to find Nth highest salary (return null if no result)

Question

e.g N is 2, I can either use densr_rank() or max to find second highest salary from Employee table.

select max(salary) as SecondHighestSalary from employee 
where salary < (select max(salary) from employee) 

above query works perfect, given the condition that if there is no second highest salary in the table, it returns NULL.

However I can achieve the similar results using dense_rank() function such as:

select e.salary as SecondHighestSalary 
from (
      select salary, dense_rank() OVER (order by salary desc) dr from employee) e
where dr = 2  

above query works fine given there is ONLY one second highest salary in the table, if the table has salaries [300, null, null] its returning [null, null], I need only one answer in the result set. how can I achieve that ?

follow up to the question: in this case, which is better (in terms of memory/processing time) max or dense_rank ()?

Answer 1

This is a really interesting question. You haven't specified the database. But if I assume that you have an index on salary that can be used by the query (so possibly salary desc depending on the database). Then your first query might have really good performance:

• Fetch the match salary using the index.
• Start scanning the index for values less than the max.
• Short circuit the scan because you get the max right away.

I'm not promising that all databases would generate this plan, but two index lookups would typically be faster than dense_rank() in this case.

Of course, with any question like this, you should test on your data and your database. That is really the correct answer.