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.



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: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, use your query as an inline view and select only those with rank 2.

Regards
Michel
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?

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: 7880
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
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
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: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pretty good.
Just a typo in column name in outer query.

Regards
Michel
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: 7880
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..
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: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: query regarding value
Next Topic: Regarding Case
Goto Forum:
  


Current Time: Sat Dec 10 01:11:07 CST 2016

Total time taken to generate the page: 0.05097 seconds