Solved

# how to find 2nd highest salary from table

Posted on 2012-09-10
Medium Priority
652 Views
how to find 2nd highest salary from table

could you please give me the query for that
0
• 3
• 3
• 2
• +1

LVL 35

Expert Comment

ID: 38383443
There is the brute force method:

select max(salary) from my_table where salary < (select max(salary) from my_table);

Or, this should work as well:

select salary from
(select salary, row_number() over (order by salary desc) rn from my_table)
where rn = 2;
0

LVL 74

Expert Comment

ID: 38383480
you can use the nth_value analytic

nth_value(sal,2) over(order by sal desc)
0

LVL 9

Author Comment

ID: 38383572
select max(salary) from my_table where salary < (select max(salary) from my_table);

is it reurn second highest row from the table
0

LVL 21

Expert Comment

ID: 38383584
SELECT DISTINCT (a.sal) FROM myTable A WHERE 2 = (SELECT COUNT (DISTINCT (b.sal)) FROM myTable B WHERE a.sal<=b.sal);
0

LVL 74

Expert Comment

ID: 38383588
it should. try it,

if it doesn't produce the results you expect, please post sample data that illustrates the problem
0

LVL 35

Expert Comment

ID: 38383615
It definitely should do what you want.  The only case that it would not handle is if there are 2 rows with the same value that are the highest.  Then what do you do?

If the 3 highest salaries are:

100
100
99

Which do you want? 100 or 99?  Use the analytic functions if you want 100.  If you want 99, then use the one with max.
0

LVL 74

Expert Comment

ID: 38383626
you could replace row_number with dense_rank to get 99 also
0

LVL 9

Author Comment

ID: 38383647
if i want 99 please give me a query for that
0

LVL 21

Expert Comment

ID: 38383670
SELECT DISTINCT (a.sal) FROM myTable A WHERE 3 = (SELECT COUNT (DISTINCT (b.sal)) FROM myTable B WHERE a.sal<=b.sal);
0

LVL 21

Accepted Solution

Amitkumar P earned 2000 total points
ID: 38383675
SELECT DISTINCT (a.sal) FROM myTable A WHERE 3 = (SELECT COUNT (DISTINCT (b.sal)) FROM myTable B WHERE a.sal<=b.sal);

Change the number highlighted in bold.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month14 days, 21 hours left to enroll