How can I select the record with the 2nd highest salary in database Oracle?

Question

Suppose I have a table employee with id, user_name, salary. How can I select the record with the 2nd highest salary in Oracle?

I googled it, find this solution, is the following right?:

select sal from
     (select rownum n,a.* from
        ( select distinct sal from emp order by sal desc) a)
where n = 2;

Answer 1

RANK and DENSE_RANK have already been suggested - depending on your requirements, you might also consider ROW_NUMBER():

select * from (
  select e.*, row_number() over (order by sal desc) rn from emp e
)
where rn = 2;

The difference between RANK(), DENSE_RANK() and ROW_NUMBER() boils down to:

• ROW_NUMBER() always generates a unique ranking; if the ORDER BY clause cannot distinguish between two rows, it will still give them different rankings (randomly)
• RANK() and DENSE_RANK() will give the same ranking to rows that cannot be distinguished by the ORDER BY clause
• DENSE_RANK() will always generate a contiguous sequence of ranks (1,2,3,...), whereas RANK() will leave gaps after two or more rows with the same rank (think "Olympic Games": if two athletes win the gold medal, there is no second place, only third)

So, if you only want one employee (even if there are several with the 2nd highest salary), I'd recommend ROW_NUMBER().