OraFAQ Forum: Server Administration » How to find n max salaries from each department

Home » RDBMS Server » Server Administration » How to find n max salaries from each department

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

How to find n max salaries from each department [message #372029]

Fri, 05 January 2001 01:06

GK
Messages: 22
Registered: January 2001

Junior Member

Hi
Is it possible to retrieve n maximum salaries in each department and corresponding Employee numbers in a single query.Some one out there help me.

Thanks in advance

GK

Report message to a moderator

Re: How to find n max salaries from each department [message #372049 is a reply to message #372029]

Sat, 06 January 2001 23:08

Robert Moy
Messages: 15
Registered: December 2000

Junior Member

Hello GK:

You can use this query:

SQL> run
1 select *
2 from wage
3 where salary =
4 (select Max(salary)
5* from wage)

Good Luck

Report message to a moderator

Re: How to find n max salaries from each department [message #372050 is a reply to message #372029]

Sun, 07 January 2001 06:57

Robert Moy
Messages: 15
Registered: December 2000

Junior Member

Hello GK:

This will allow you to get salary and names of more than one department if located in different tables.

SQL> run
1 select wage.name "department1 name", wage.salary "department1 salary", wage2.name "department2
2 from wage, wage2
3 where wage.salary =
4 (select Max(wage.salary)
5 from wage)
6 and
7 wage2.salary =
8 (select Max(wage2.salary)
9* from wage2)

Good Luck

Report message to a moderator

Re: How to find n max salaries from each department [message #372054 is a reply to message #372050]

Mon, 08 January 2001 05:23

GK
Messages: 22
Registered: January 2001

Junior Member

Hi my requirement is like this

empno sal deptno
1 10000 1
2 7500 1
3 5000 2
4 10500 3
5 6000 1
6 3000 2
7 4000 2
8 12000 2
9 7200 2
10 5000 1

now what I want is

deptno empno sal
1 1 10000
1 2 7500
1 5 6000
2 8 12000
2 9 7200
2 3 5000
3 4 10500

I hope u got my requirement clearly

I would be thankful if u could give me a query to solve this.

.::GK::.

Report message to a moderator

Re: How to find n max salaries from each department [message #372092 is a reply to message #372029]

Tue, 09 January 2001 21:49

Robert Moy
Messages: 15
Registered: December 2000

Junior Member

Hello GK:

I am sorry that I misunderstood the problem. Here is the code to solve the problem. The number 3 will get three highest salaries. That would be the n to find the max salaries.

select deptno, sal, empno
from wage3 w1
where 3 >
(select count(*)
from wage3 w2
where w1.sal < w2.sal
and
w2.deptno=1)
and
w1.deptno=1
or
3 >
(select count(*)
from wage3 w2
where w1.sal < w2.sal
and
w2.deptno=2)
and
w1.deptno=2
or
3 >
(select count(*)
from wage3 w2
where w1.sal < w2.sal
and
w2.deptno=3)
and
w1.deptno=3
order by deptno, sal desc
/

Good Luck

Report message to a moderator

Re: How to find n max salaries from each department [message #372566 is a reply to message #372050]

Fri, 23 February 2001 02:18

Balamurugan
Messages: 4
Registered: February 2001

Junior Member

Hai ,

try this query,

select deptno,empno,sal from emp group by deptno,empno,sal order by deptno,sal desc;

run this query this will give the answer that u want.

If it works send me.

With regards,
Balu

Report message to a moderator

Previous Topic:	Indexes
Next Topic:	....for update

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 04:47:06 CDT 2024