OraFAQ Forum: SQL & PL/SQL » Select max 3 sal

Home » SQL & PL/SQL » SQL & PL/SQL » Select max 3 sal

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

Select max 3 sal [message #20963]

Wed, 03 July 2002 03:54

Sanjay
Messages: 236
Registered: July 2000

Senior Member

please give me the sql by which i can select the highest 3 salaried persor from emp table in Oracle 7.
also give in 8 and 8i version also.

Report message to a moderator

Re: Select max 3 sal [message #20972 is a reply to message #20963]

Wed, 03 July 2002 22:45

Satish Shrikhande
Messages: 167
Registered: October 2001

Senior Member

This is rough idea , better you tune this SQL

select ename,sal from
emp where
sal in(
Select sal
From (select abs (-a.sal) sal
From EMP a
Group by -sal)
Where rownum<3)
order by 2 desc

Report message to a moderator

Re: Select max 3 sal [message #20987 is a reply to message #20972]

Thu, 04 July 2002 20:30

Ravi
Messages: 251
Registered: June 1998

Senior Member

This is an easy one :
select ename,sal from (select ename,sal from emp order by sal desc) where rownum<4;

Report message to a moderator

Re: Select max 3 sal [message #20994 is a reply to message #20963]

Fri, 05 July 2002 03:07

Paramaguru
Messages: 4
Registered: June 2001

Junior Member

Hi,
Pls try the following query..

SELECT a.sal
FROM emp a
WHERE 3=
(SELECT count(distinct(sal))
FROM emp b
WHERE a.sal <= b.sal)

if u want 4th maximum Replace 3 by 4

regards
Paramaguru.N

Report message to a moderator

Previous Topic:	help in simple plsql
Next Topic:	Re: ORA-12560: TNS:protocol adapter error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 12:30:43 CDT 2024