Home » SQL & PL/SQL » SQL & PL/SQL » Query to get 5th Max salary
Query to get 5th Max salary [message #211488] Thu, 28 December 2006 23:23 Go to next message
kishore_p
Messages: 8
Registered: December 2006
Junior Member
Hi,

This is kishore. Can anybody tell me how to write a query to get a 5th maximum salary from the EMP table.

Thanks and Regards,
Kishore.
Re: Query to get 5th Max salary [message #211497 is a reply to message #211488] Fri, 29 December 2006 00:23 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Try this

select * from emp where sal=(select max(sal) from (select * from emp order by sal)
where rownum<=5)

Shahnaz
Re: Query to get 5th Max salary [message #211498 is a reply to message #211488] Fri, 29 December 2006 00:26 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member


Select distinct(sal)
from tablename
where 5=(select count(distinct(sal)
from tablename);
Re: Query to get 5th Max salary [message #211525 is a reply to message #211498] Fri, 29 December 2006 03:08 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
SELECT SAL
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) RN, SAL
          FROM EMP)
 WHERE RN = 5;

[Updated on: Fri, 29 December 2006 04:54]

Report message to a moderator

Re: Query to get 5th Max salary [message #211531 is a reply to message #211488] Fri, 29 December 2006 04:17 Go to previous messageGo to next message
pavan_034
Messages: 16
Registered: October 2006
Junior Member
TRY THIS CODE

SELECT * FROM EMP
WHERE
&N=(SELECT COUNT(DISTINCT(SAL)) FROM EMP E
WHERE
E.SAL>=EMP.SAL);

WHEN YOU EXECUTE THIS QUERY, IT ASKS FOR THE VALUE OF N.
ENTER THE VALUE AS PER YOUR REQUIREMENT.IN YOUR CASE ENTER 5.

BYE.
Re: Query to get 5th Max salary [message #211536 is a reply to message #211488] Fri, 29 December 2006 04:47 Go to previous messageGo to next message
jilhewar_santosh
Messages: 13
Registered: July 2006
Location: mumbai
Junior Member
Hi kishor,

SELECT sal FROM
(
SELECT dense_rank() over(ORDER BY sal DESC) rank,sal FROM emp
)
WHERE rank=5

i hope it will work for u
Re: Query to get 5th Max salary [message #211537 is a reply to message #211488] Fri, 29 December 2006 04:56 Go to previous messageGo to next message
jilhewar_santosh
Messages: 13
Registered: July 2006
Location: mumbai
Junior Member
SELECT sal FROM
(
SELECT ROWNUM rno, sal FROM
(SELECT DISTINCT sal FROM emp ORDER BY sal DESC)
) WHERE rno=5
icon14.gif  Re: Query to get 5th Max salary [message #211692 is a reply to message #211488] Mon, 01 January 2007 05:05 Go to previous messageGo to next message
nabbu
Messages: 5
Registered: December 2006
Junior Member

well this code may help,you can try this .
select sal from emp where sal=max(sal) limit 1,5;
Re: Query to get 5th Max salary [message #211695 is a reply to message #211692] Mon, 01 January 2007 05:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It might, but not in Oracle database.
Re: Query to get 5th Max salary [message #211747 is a reply to message #211695] Tue, 02 January 2007 01:51 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
A classic question that gets a classic reply: search the board, look at the FAQ pages, this one's closed.

http://www.orafaq.com/forum/fa/448/0/

MHE
Previous Topic: pl/sql code
Next Topic: Conflict between versions of oracle
Goto Forum:
  


Current Time: Fri Dec 09 12:05:17 CST 2016

Total time taken to generate the page: 0.17875 seconds