How Can I Select The Record With The 2nd Highest Salary In Database Oracle?

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 ri

Solution 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 (orderby 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().

Solution 2:

If you're using Oracle 8+, you can use the RANK() or DENSE_RANK() functions like so

SELECT*FROM (
  SELECT some_column, 
         rank() over (orderby your_sort_column desc) as row_rank
) t
WHERE row_rank =2;

Solution 3:

This query works in SQL*PLUS to find out the 2nd Highest Salary -

SELECT*FROM EMP
WHERE SAL = (SELECTMAX(SAL) FROM EMP
WHERE SAL < (SELECTMAX(SAL) FROM EMP));

This is double sub-query.

I hope this helps you..

Solution 4:

WITH records
AS
(
    SELECT  id, user_name, salary,
            DENSE_RANK() OVER (PARTITIONBY id ORDERBY salary DESC) rn
    FROM    tableName
)
SELECT  id, user_name, salary
FROM    records 
WHERE   rn =2

• DENSE_RANK()

Solution 5:

You should use something like this:

SELECT*FROM (select salary2.*, rownum rnum from
         (select*from salary ORDERBY salary_amount DESC) salary2
  where rownum <=2 )
WHERE rnum >=2;