?
Solved

how to find 2nd highest salary from table

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

could you please give me the query for that
0
Comment
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 35

Expert Comment

by:johnsone
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

by:sdstuber
ID: 38383480
you can use the nth_value analytic

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

Author Comment

by:Manikandan Thiagarajan
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:Amitkumar P
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

by:sdstuber
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

by:johnsone
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

by:sdstuber
ID: 38383626
you could replace row_number with dense_rank to get 99 also
0
 
LVL 9

Author Comment

by:Manikandan Thiagarajan
ID: 38383647
if i want 99 please give me a query for that
0
 
LVL 21

Expert Comment

by:Amitkumar P
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

by:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question