OraFAQ Forum: SQL & PL/SQL » Highest salary

Home » SQL & PL/SQL » SQL & PL/SQL » Highest salary

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

Highest salary [message #19396]

Thu, 14 March 2002 07:24

aish
Messages: 44
Registered: March 2001

Member

Hi

Can anybody tell me how do I find the highest three salary from a table.

Thanx
Aish

Report message to a moderator

Re: Highest salary [message #19397 is a reply to message #19396]

Thu, 14 March 2002 07:36

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

try this(it uses scott.emp table)

select * from
(select emp.* ,row_number() over (order by sal desc ) rn
from emp)
where rn<4

Report message to a moderator

Re: Highest salary [message #19406 is a reply to message #19396]

Thu, 14 March 2002 18:16

jeya
Messages: 18
Registered: January 2001

Junior Member

select sal from emp e where 2=(select sal from emp where sal>e.sal)

note: if there are two rows having the same salary (say there are two employees getting the first highest salary )then there won't be second highest salary.Next we can get only the third highest salary.

Report message to a moderator

Re: Highest salary [message #19411 is a reply to message #19396]

Thu, 14 March 2002 21:02

Srihari
Messages: 22
Registered: October 2000

Junior Member

Try this Query

select distinct(sal) from emp e where 3>
(select count(distinct(sal)) from emp where sal>e.sal)

Note : this query results in distinct highest 3 salaries no matter there may be more no of people drawing the same salaries.

Hope this helps U

Srihari

Report message to a moderator

Re: Highest salary-Sorry [message #19412 is a reply to message #19396]

Thu, 14 March 2002 21:29

Srihari
Messages: 22
Registered: October 2000

Junior Member

Hi aish

Im sorry Last reply was not complete.We have to use order by salary descending

select distinct(sal) from emp e where 3>
(select count(distinct(sal)) from emp where sal>e.sal)
order by sal desc;

Srihari

Report message to a moderator

Re: Highest salary [message #19421 is a reply to message #19396]

Fri, 15 March 2002 06:05

Sri DHAR
Messages: 22
Registered: March 2002

Junior Member

Simplest version is,

select * from
(select * from
emp order by sal desc)
Where rownum <= 3

Thanx

Report message to a moderator

Re: Highest salary [message #19433 is a reply to message #19396]

Sun, 17 March 2002 17:35

jeya
Messages: 18
Registered: January 2001

Junior Member

sorrry for the mistake in my previous message

select sal from emp e where 2=(select count(*) from emp where sal>e.sal)

Report message to a moderator

Re: Highest salary [message #19445 is a reply to message #19433]

Mon, 18 March 2002 08:33

Sri DHAR
Messages: 22
Registered: March 2002

Junior Member

jeya,
I think the requirement was to get,
TOP n Salaries from Emp table.
Your query gives the nth Salary from Emp table.
regards,
Sri DHAR

Report message to a moderator

Previous Topic:	Calling PL/SQL stored procedure from shell
Next Topic:	PL/SQL Question.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:58:58 CDT 2024