ORACLE SQL Second Maximum Salary

Question

How to find Second Maximum salary drawn by the employee from Employees table. Whether is it possible to find it through ROWNUM function.

example table:

S.no    Name       employee_id     salary
201     Steven        100            1000
202     Alexander     101            2500 
203     Daniel        102            5000
204     John          103            3000
205     Ishanth       104            8000

Yes, it is. Can you provide sample rows from which you are trying to find second maximum salary? — Vash, Dec 29 '17 at 06:04

score 5 · Answer 1 · edited Dec 29 '17 at 06:47

5

Since you mentioned second highest salary, you should be using DENSE_RANK instead of ROW_NUMBER if there are two employees with the same-highest value which will give you the highest salary by mistake even when you give WHERE ROW_NUMBER = 2 condition.

SELECT A.S_NO, A.NAME, A.EMPLOYEE_ID, A.SALARY
FROM
    (SELECT S_NO, NAME, EMPLOYEE_ID, SALARY, 
     DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK) A
WHERE A.SALARY_RANK = 2;

edited Dec 29 '17 at 06:47

APC

144,005
19
170
281

answered Dec 29 '17 at 06:18

Vash

1,767
2
12
19

3

Don't know why somebody downvoted this, it's a perfectly good answer. – APC Dec 29 '17 at 06:48
This is frustrating! – Vash Dec 29 '17 at 06:53
The only fix I would mention would be to add the missing `FROM` like `AS SALARY_RANK FROM A)`. That provided me the correct answer with your implementation. – Tanner Dolby Jan 26 '21 at 17:51

score 1 · Answer 2 · edited Dec 29 '17 at 08:56

1

Try this

SELECT salary FROM (SELECT DISTINCT salary FROM Employees  ORDER BY salary 
DESC) WHERE ROWNUM=2;

Where,

salary is the column Name
Employees is the table name

or

Simplest way to fetch second max salary & nth salary

select 
 DISTINCT(salary) 
from Employees 
 order by salary desc 
limit 1,1

Note:

limit 0,1  - Top max salary

limit 1,1  - Second max salary

limit 2,1  - Third max salary

limit 3,1  - Fourth max salary

Update for oracle

SELECT salary FROM (
    SELECT salary, row_number() OVER (order by salary desc) AS rn FROM Employees
)
WHERE rn = 2

edited Dec 29 '17 at 08:56

Ankit Bajpai

13,128
4
25
40

answered Dec 29 '17 at 06:08

Ramesh Rajendran

37,412
45
153
234

upvoted but oracle doesn't support the `limit` – Rams Dec 29 '17 at 06:16
You are adding sql tag for this question.that's why I gave this answer in sql . Please find a way to limit in oracle – Ramesh Rajendran Dec 29 '17 at 06:17
@APC I have update my answer. – Ramesh Rajendran Dec 29 '17 at 06:42
@Rams I have update my answer – Ramesh Rajendran Dec 29 '17 at 06:43
2

`ROWNUM=2` doesn't work the way you think it does; that query will return **zero** rows. – APC Dec 29 '17 at 06:45
@APC, Greate Catch. – Ankit Bajpai Dec 29 '17 at 08:57

Eralper · Answer 3 · 2017-12-29T10:41:47.883

1

I guess, you can use Nth_Value function here Please refer to https://docs.oracle.com/cloud/latest/db112/SQLRF/functions114.htm#SQLRF30031 for syntax

select distinct 
nth_value(salary,2) OVER (ORDER BY salary desc range between unbounded preceding and unbounded following) as secondVal 
from HR.EMPLOYEES;

Another option, is using DENSE_RANK function as follows

with cte as (
    select salary, dense_rank() over (order by salary desc) as nth_salary from Employee
)
select salary from cte 
where 
    nth_salary = 2;

Here is the output

edited Dec 29 '17 at 10:41

answered Dec 29 '17 at 06:18

Eralper

6,461
2
21
27

What happens if there are two employees with the highest salary? – APC Dec 29 '17 at 09:45
Let's say if there are two max values, assume top 2 salaries are same with 100, then this query will return 100. Not the following one. – Eralper Dec 29 '17 at 10:34

score 0 · Answer 4 · answered Dec 29 '17 at 06:28

0

Use general query for max nth salary:

select salary from table_name order by salary desc limit n-1,1;

answered Dec 29 '17 at 06:28

P3arl

383
3
18

score 0 · Answer 5 · answered Dec 29 '17 at 06:30

Rather than using ROWNUM .You can easily find the 'n' th salary by

SELECT *
  FROM (
  SELECT DISTINCT  salary 
  FROM Employees
  ORDER BY salary DESC limit 2
  ) a
ORDER BY a.salary limit 1

Here instead of "2" you can give any "n" value you required.

ORACLE SQL Second Maximum Salary

5 Answers5

Update for oracle