[sql] 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 right?:

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

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.

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

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;

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

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

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;

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.

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

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

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

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

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;

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)

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
)

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

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
               );

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

this will work for all DBs.