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

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

(OR)

SELECT ENAME ,SAL  FROM EMP  ORDER BY SAL DESC;

(OR)

SELECT * FROM(SELECT ENAME,SAL ,DENSE_RANK() 
OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP) WHERE R=2;

Answer 2

You can use two max function. Let's say get data of userid=10 and its 2nd highest salary from SALARY_TBL.

select max(salary) from SALARY_TBL
where 
userid=10
salary <> (select max(salary) from SALARY_TBL where userid=10)

Answer 3

I would suggest following two ways to implement this in Oracle.

1. Using Sub-query:

select distinct SALARY   
from EMPLOYEE e1  
where 1=(select count(DISTINCT e2.SALARY) from EMPLOYEE e2 where         
  e2.SALARY>e1.SALARY);

This is very simple query to get required output. However, this query is quite slow as each salary in inner query is compared with all distinct salaries.

2. Using DENSE_RANK():

select distinct SALARY   
from
  (
    select e1.*, DENSE_RANK () OVER (order by SALARY desc) as RN 
    from EMPLOYEE e
  ) E
 where E.RN=2;

This is very efficient query. It works well with DENSE_RANK() which assigns consecutive ranks unlike RANK() which assigns next rank depending on row number which is like olympic medaling.

Difference between RANK() and DENSE_RANK(): https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions

Answer 4

select max(Salary) from EmployeeTest where Salary < ( select max(Salary) from EmployeeTest ) ;

this will work for all DBs.

Answer 5

select Max(Salary) as SecondHighestSalary from Employee where Salary not in (select max(Salary) from Employee)

Answer 6

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

This is more optimum, it suits all scenarios...

Answer 7

select * FROM (
select EmployeeID, Salary
, dense_rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = 2;

dense_rank() is used for the salary has to be same.So it give the proper output instead of using rank().

Answer 8

This query helps me every time for problems like this. Replace N with position..

select *
from(
     select *
     from (select * from TABLE_NAME order by SALARY_COLUMN desc)
     where rownum <=N
    )
where SALARY_COLUMN <= all(
                select SALARY_COLUMN
                from (select * from TABLE_NAME order by SALARY_COLUMN desc)
                where rownum <=N
               );

Answer 9

select salary from EmployeeDetails order by salary desc limit 1 offset (n-1).

If you want to find 2nd highest than replace n with that 2.

Answer 10

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

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

This is double sub-query.

I hope this helps you..

Answer 11

I believe this will accomplish the same result, without a subquery or a ranking function:

SELECT *
FROM emp
ORDER BY sal DESC
LIMIT 1
OFFSET 2

Answer 12

select * from emp where sal=(select max(sal) from emp where sal<(select max(sal) from emp))

so in our emp table(default provided by oracle) here is the output

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

  7698 BLAKE      MANAGER         7839 01-MAY-81       3000            30
  7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
  7902 FORD       ANALYST         7566 03-DEC-81       3000            20

or just you want 2nd maximum salary to be displayed

select max(sal) from emp where sal<(select max(sal) from emp)

MAX(SAL)

  3000

Answer 13

Syntax it for Sql server

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP n-1 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)

To get 2nd highest salary of employee then we need replace “n” with 2 our query like will be this

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP 1 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)

3rd highest salary of employee

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP 2 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)

Answer 14

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

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

Answer 15

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

Answer 16

Replace N with your Highest Number

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

Explanation

The query above can be quite confusing if you have not seen anything like it before – the inner query is what’s called a correlated sub-query because the inner query (the subquery) uses a value from the outer query (in this case the Emp1 table) in it’s WHERE clause.

And Source

I have given the answer here

By the way I am flagging this Question as Duplicate.

Answer 17

WITH records
AS
(
    SELECT  id, user_name, salary,
            DENSE_RANK() OVER (PARTITION BY id ORDER BY salary DESC) rn
    FROM    tableName
)
SELECT  id, user_name, salary
FROM    records 
WHERE   rn = 2

• DENSE_RANK()

Answer 18

You should use something like this:

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