OraFAQ Forum: SQL & PL/SQL » Query to get 5th Max salary

Home » SQL & PL/SQL » SQL & PL/SQL » Query to get 5th Max salary

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to get 5th Max salary [message #211488]

Thu, 28 December 2006 23:23

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.

Report message to a moderator

Re: Query to get 5th Max salary [message #211497 is a reply to message #211488]

Fri, 29 December 2006 00:23

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

Report message to a moderator

Re: Query to get 5th Max salary [message #211498 is a reply to message #211488]

Fri, 29 December 2006 00:26

romi
Messages: 67
Registered: October 2006

Member

Select distinct(sal)
from tablename
where 5=(select count(distinct(sal)
from tablename);

Report message to a moderator

Re: Query to get 5th Max salary [message #211525 is a reply to message #211498]

Fri, 29 December 2006 03:08

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

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.

Report message to a moderator

Re: Query to get 5th Max salary [message #211536 is a reply to message #211488]

Fri, 29 December 2006 04:47

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

Report message to a moderator

Re: Query to get 5th Max salary [message #211537 is a reply to message #211488]

Fri, 29 December 2006 04:56

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

Report message to a moderator

Re: Query to get 5th Max salary [message #211692 is a reply to message #211488]

Mon, 01 January 2007 05:05

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;

Report message to a moderator

Re: Query to get 5th Max salary [message #211695 is a reply to message #211692]

Mon, 01 January 2007 05:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It might, but not in Oracle database.

Report message to a moderator

Re: Query to get 5th Max salary [message #211747 is a reply to message #211695]

Tue, 02 January 2007 01:51

Maaher
Messages: 7065
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.

MHE

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 23:14:46 CST 2024