Home » SQL & PL/SQL » SQL & PL/SQL » get the second highest salary (oracle 9i)
get the second highest salary [message #302497] Mon, 25 February 2008 23:38 Go to next message
sudharshan
Messages: 48
Registered: November 2006
Member

select ename,empno,
rank() over(partition by sal desc) rank
from emp


In my output i want to see only the second highest not all other ranks?is this possible.

Report message to a moderator

Re: get the second highest salary [message #302506 is a reply to message #302497] Tue, 26 February 2008 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, use your query as an inline view and select only those with rank 2.

Regards
Michel

Report message to a moderator

Re: get the second highest salary [message #302522 is a reply to message #302497] Tue, 26 February 2008 00:51 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
can you please show me with an example?

Report message to a moderator

Re: get the second highest salary [message #302523 is a reply to message #302522] Tue, 26 February 2008 00:52 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Why don't you try it yourself?
Google for inline view if you don't know what that is.
Try it, and if you're stuck come back and show us what you tried.
Copy your sqlplus output here, surrounded by [code][/code] tags

Report message to a moderator

Re: get the second highest salary [message #302616 is a reply to message #302497] Tue, 26 February 2008 06:28 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
 
select ename,emp from
(
select ename,empno,rank() over(order by sal desc)rank
from emp) 
where rank=2

Report message to a moderator

Re: get the second highest salary [message #302621 is a reply to message #302616] Tue, 26 February 2008 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Pretty good.
Just a typo in column name in outer query.

Regards
Michel

Report message to a moderator

Re: get the second highest salary [message #302625 is a reply to message #302621] Tue, 26 February 2008 06:47 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
That's probably because he is not the original poster answering, but someone who thought we were unable to provide the answer..

Report message to a moderator

Re: get the second highest salary [message #302630 is a reply to message #302625] Tue, 26 February 2008 06:54 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Doh! I didn't notice this is not the same one, I thought it was OP sharing its findings with us.

Regards
Michel

Report message to a moderator

Previous Topic: query regarding value
Next Topic: Regarding Case
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Feb 07 00:04:03 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.