How to Select The Record with The 2Nd Highest Salary in Database Oracle

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

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

Selecting the record(s) with the second highest something

Window functions are the built-in functionality to do this. In particular, dense_rank():

select e.*
from (select e.*, dense_rank() over (order by salary desc) as seqnum
      from employee e
     ) e
where seqnum = 2;

Nth max salary in Oracle

try this

select *
  from
  (
    select
        sal
          ,dense_rank() over (order by sal desc) ranking
    from   table
  )
  where ranking = 4 -- Replace 4 with any value of N

I need to fetch the second highest salary per department using correlated subquery and oracle sql

I don't have your tables so I'll use Scott's EMP. This is its contents:

SQL> select deptno, ename, sal from emp order by deptno, sal desc;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450   --> 2nd highest in deptno 10
        10 MILLER           1300
        20 SCOTT            3000
        20 FORD             3000
        20 JONES            2975   --> 2nd highest in deptno 20
        20 ADAMS            1100
        20 SMITH             800
        30 BLAKE            2850
        30 ALLEN            1600   --> 2nd highest in deptno 30
        30 TURNER           1500
        30 MARTIN           1250
        30 WARD             1250
        30 JAMES             950

14 rows selected.

This is what you don't want:

SQL> with temp as
  2    (select deptno, ename, sal,
  3       dense_rank() over (partition by deptno order by sal desc) rnk
  4     from emp
  5    )
  6  select *
  7  from temp
  8  where rnk = 2
  9  order by deptno, sal desc;

    DEPTNO ENAME             SAL        RNK
---------- ---------- ---------- ----------
        10 CLARK            2450          2
        20 JONES            2975          2
        30 ALLEN            1600          2

SQL>

---

OK, let's correlate some subqueries, then. Return employees whose salary is

• lower than the highest in their department (line #6) (it would rank as the 1st)
• the highest for the rest of salaries in their department (line #3)

So:

SQL> select e.deptno, e.ename, e.sal
  2  from emp e
  3  where e.sal = (select max(b.sal)
  4                 from emp b
  5                 where b.deptno = e.deptno
  6                   and b.sal < (select max(a.sal)
  7                                from emp a
  8                                where a.deptno = b.deptno
  9                                group by a.deptno
 10                               )
 11                 )
 12  order by e.deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        20 JONES            2975
        30 ALLEN            1600

SQL>

How to get second largest or third largest entry from a table

SELECT *
FROM (
  SELECT some_column, 
         row_number() over (order by your_sort_column desc) as row_num
  FROM some_table
) t
WHERE row_num = 3

If you expect more than one row to have the same value in your_sort_column you can also use the rank() function

SELECT *
FROM (
  SELECT some_column, 
         rank() over (order by your_sort_column desc) as row_rank
  FROM some_table
) t
WHERE row_rank = 3

This migh return more than one row..

How to get second-highest salary employees in a table

To get the names of the employees with the 2nd highest distinct salary amount you can use.

;WITH T AS
(
SELECT *,
       DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk
FROM Employees
)
SELECT Name
FROM T
WHERE Rnk=2;

If Salary is indexed the following may well be more efficient though especially if there are many employees.

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

Test Script

CREATE TABLE Employees
  (
     Name   VARCHAR(50),
     Salary FLOAT
  )

INSERT INTO Employees
SELECT TOP 1000000 s1.name,
                   abs(checksum(newid()))
FROM   sysobjects s1,
       sysobjects s2

CREATE NONCLUSTERED INDEX ix
  ON Employees(Salary)

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT MIN(Salary)
                 FROM   (SELECT DISTINCT TOP (2) Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) T);

WITH T
     AS (SELECT *,
                DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk
         FROM   Employees)
SELECT Name
FROM   T
WHERE  Rnk = 2;

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT DISTINCT TOP (1) Salary
                 FROM   Employees
                 WHERE  Salary NOT IN (SELECT DISTINCT TOP (1) Salary
                                       FROM   Employees
                                       ORDER  BY Salary DESC)
                 ORDER  BY Salary DESC)

SELECT Name
FROM   Employees
WHERE  Salary = (SELECT TOP 1 Salary
                 FROM   (SELECT TOP 2 Salary
                         FROM   Employees
                         ORDER  BY Salary DESC) sel
                 ORDER  BY Salary ASC)  

---

Related Topics