title: dense_rank() and max, which one is better to find Nth highest salary (return null if no result)
body: <p>e.g N is 2, I can either use densr_rank() or max to find second highest salary from Employee table.</p>
  <pre><code>select max(salary) as SecondHighestSalary from employee 
  where salary &lt; (select max(salary) from employee) 
  </code></pre>
  <p>above query works perfect, given the condition that if there is no second highest salary in the table, it returns NULL.</p>
  <p>However I can achieve the similar results using dense_rank() function such as:</p>
  <pre><code>select e.salary as SecondHighestSalary 
  from (
        select salary, dense_rank() OVER (order by salary desc) dr from employee) e
  where dr = 2  
  </code></pre>
  <p>above query works fine given there is ONLY one second highest salary in the table, if the table has salaries <code>[300, null, null]</code> its returning <code>[null, null]</code>, I need only one answer in the result set. how can I achieve that ?</p>
  <p>follow up to the question: in this case, which is better (in terms of memory/processing time)
  max or dense_rank ()?</p>