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;

score 28 · Accepted Answer · answered Apr 30 '13 at 06:56

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

+1 to Gave a good understanding between RANK and DENSE_RANK with 'Olymic games' example. — janasainik, Nov 11 '13 at 09:37
Above row_number() function query will give wrong result if two employees have the same highest salary. We can you dense_rank() analytic function to get desired result. — Tarun Kumar, Aug 17 '18 at 14:35
@TarunKumar As I had mentioned in my answer - depending on your requirements, you need either `rank / dense_rank` to get all employees with the highest salary or `row_number` if you want to ensure that you always get exactly one record. — Frank Schmitt, Aug 18 '18 at 11:59

hd1 · Answer 2 · 2013-04-30T07:11:32.817

5

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;

edited Apr 30 '13 at 07:11

answered Apr 30 '13 at 06:12

hd1

33,938
5
80
91

I mistyped 9, meant 8, it's been corrected. Thanks for pointing it out! – hd1 Apr 30 '13 at 07:12
NB This query can return more than one row while the query posted in the question which will return the does a distinct to filter duplicates. – Colin 't Hart Apr 30 '13 at 07:24

score 4 · Answer 3 · answered Nov 16 '14 at 19:24

4

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

answered Nov 16 '14 at 19:24

Sanchit

1,145
11
6

score 2 · Answer 4 · answered Apr 30 '13 at 06:13

2

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

answered Apr 30 '13 at 06:13

John Woo

258,903
69
498
492

score 1 · Answer 5 · answered Apr 30 '13 at 06:10

1

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;

answered Apr 30 '13 at 06:10

Linga

10,379
10
52
104

Why do I have to use it as "rnum<=2" and "rnum>=2", can't I just say "rnum=2"? – user1801838 Apr 30 '13 at 07:02
@user1801838 No. You cannot use rownum with = n for n > 1 (in fact you can, but it will always return an empty result set). – Frank Schmitt Apr 30 '13 at 07:18

score 1 · Answer 6 · edited Oct 07 '13 at 13:24

1

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)

edited Oct 07 '13 at 13:24

kleopatra

51,061
28
99
211

answered Oct 07 '13 at 13:13

shashank

15
1

please learn to use the format tags and use them to make the answers readable :-) – kleopatra Oct 07 '13 at 13:24

Md Wasi · Answer 7 · 2017-01-11T06:16:57.063

1

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

edited Jan 11 '17 at 06:16

answered Jan 07 '17 at 06:38

Md Wasi

479
3
16

score 1 · Answer 8 · answered Apr 03 '17 at 20:07

1

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

answered Apr 03 '17 at 20:07

pradsav

61
4

score 1 · Answer 9 · edited Feb 26 '18 at 07:37

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

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.

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

score 0 · Answer 10 · answered Sep 29 '14 at 19:15

0

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

answered Sep 29 '14 at 19:15

Jeff Wu

2,428
1
21
25

FYI, LIMIT keyword is not available on Oracle database, secondly, it would give wrong result if two employees have the same highest salary. We can you dense_rank() analytic function to get desired result. – Tarun Kumar Aug 17 '18 at 14:21

score 0 · Answer 11 · answered Jun 12 '16 at 19:45

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

score 0 · Answer 12 · edited Feb 15 '17 at 18:09

0

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

edited Feb 15 '17 at 18:09

laylarenee

3,276
7
32
40

answered Feb 15 '17 at 18:07

Rajkumar Pandey

1

score 0 · Answer 13 · edited Nov 24 '17 at 14:04

0

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

this will work for all DBs.

edited Nov 24 '17 at 14:04

Smittey

2,475
10
28
35

answered Nov 24 '17 at 13:59

Ali Saithumuhammed

1

score 0 · Answer 14 · answered Jul 31 '19 at 13:37

0

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)

answered Jul 31 '19 at 13:37

Kyung Hwan Min

357
3
5

score -1 · Answer 15 · edited May 23 '17 at 12:09

-1

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.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 30 '13 at 06:17

Prahalad Gaggar

11,389
16
53
71

-1. 361 consistent gets for a sample `hr.employees` table with 107 rows. – Kirill Leontev Apr 30 '13 at 06:25
2

there is no error, your approach is dramatically suboptimal from performance point of view. for instance @hd1's query reads only 6 blocks – Kirill Leontev Apr 30 '13 at 06:27

score -1 · Answer 16 · answered Jul 29 '13 at 07:28

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
)

score -1 · Answer 17 · edited Mar 25 '21 at 05:39

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

edited Mar 25 '21 at 05:39

artemis

6,857
11
46
99

answered Mar 09 '19 at 21:17

Vvn Bhaskar Vvn Bhaskar

1
1

2

Please augment this code-only answer with some explanation. – Yunnosch Mar 09 '19 at 21:23
You chose to undo a helpful edit, which demonstrated less ambiguos formatting. Now try yourself to fix it. https://stackoverflow.com/editing-help#comment-formatting – Yunnosch Mar 09 '19 at 21:41

score -2 · Answer 18 · edited Jan 02 '15 at 10:49

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

edited Jan 02 '15 at 10:49

matsjoyce

5,744
6
31
38

answered Jan 02 '15 at 10:01

Ajit Deswal

1

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

18 Answers18

MAX(SAL)

Linked

Related