Home » SQL & PL/SQL » SQL & PL/SQL » Top 5 salary by departments
Top 5 salary by departments [message #234304] Mon, 30 April 2007 15:19 Go to next message
jeannezhang
Messages: 21
Registered: May 2005
Junior Member
Hi, SQL experts,
I need help. I want to like top 5 employees with highest salary for each department. For example, we have 3 departments A, B, and C. I would like to have:

Deparment A:
emp1 $100,000
emp2 $99,000
emp3 $98,200
emp4 $89,999
emp5 $85,300

Deparment B:
emp6 $120,000
emp7 $100,900
emp8 $98,000
emp9 $97,000
emp10 $89,900

Deparment C:
emp11 $45,000
emp12 $35,999
emp13 $35,900
emp14 $34,000
emp15 $30,000

Thanks a lot for any help.
Re: Top 5 salary by departments [message #234308 is a reply to message #234304] Mon, 30 April 2007 15:44 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Such a question is asked quite often; use OraFAQ's search facility and you'll find the answer. You might also check FAQ section which covers top n queries.
Re: Top 5 salary by departments [message #234309 is a reply to message #234304] Mon, 30 April 2007 15:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Jeanne,

Top N query is one the of most asked questions.
Please read How to get a quick answer to your question: TIPS AND TRICKS and search before posting.

Regards
Michel
Re: Top 5 salary by departments [message #234310 is a reply to message #234308] Mon, 30 April 2007 15:51 Go to previous messageGo to next message
jeannezhang
Messages: 21
Registered: May 2005
Junior Member
I know how to like top 5. That's easy for me. My question is how to list top 5 in each deparment. Please see the example I list.
Thanks.
Re: Top 5 salary by departments [message #234311 is a reply to message #234310] Mon, 30 April 2007 15:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One question: what if there are multiple employees with same salaries?
For example, three salaries are 1000 and three are 2000?

Regards
Michel
Re: Top 5 salary by departments [message #234314 is a reply to message #234311] Mon, 30 April 2007 16:01 Go to previous messageGo to next message
jeannezhang
Messages: 21
Registered: May 2005
Junior Member
I am sorry. I gave wrong examples. I wanted to count how many employees with top salary range.
For example,

Department A:
$100,000 1 employee
$90,000 1 employee
$80,000 3 employees
$70,000 6 employees
$60,000 10 employees

Deparment B:
$40,000 10 employees
%38,000 5 employees
...

Department C:
....

Thanks a lot for any help.
Re: Top 5 salary by departments [message #234317 is a reply to message #234314] Mon, 30 April 2007 16:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not just count(*) group by department and salary?

Regards
Michel
Re: Top 5 salary by departments [message #234318 is a reply to message #234314] Mon, 30 April 2007 16:10 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or group by department and trunc(salary,-4) if you want per range of 10,000$.

Regards
Michel
Previous Topic: gap analysis for 9i to 10 g migration
Next Topic: Generalized stored procedure to truncate some tables owned by another Schema
Goto Forum:
  


Current Time: Mon Dec 05 10:48:59 CST 2016

Total time taken to generate the page: 0.05549 seconds