CODEWITHSUNDEEP
sqloracle-databaseoracle11gsubquery
mthomp81
mthomp81

Reputation: 63

Oracle 11g: Write a query that lists the highest earners for each department

This is a problem I've spent hours on now, and tried various different ways. It HAS to use Subqueries.

"Write a query that lists the highest earners for each department. Include the last_name, department_id, and the salary for each employee."

I've done a ton of subquery methods, and nothing works. I either get an error, or "No rows return". I'm assuming because one of the department_id is null, but even with NVL(department_id), I'm still having trouble. I tried splitting the table, and had no luck. Textbook's no help, my instructor is kind of useless, please... any help at all.

Here's a snapshot of the values, if that helps. https://www.dropbox.com/s/bxtntlzqixdizzp/helpme.png?dl=0

Upvotes: 0

Views: 870

Answers (1)

RealDealNeill
RealDealNeill

Reputation: 64

You can rank the values within each department - then pull only the first place ranks in the outer query. 

select a.last_name
,a.department_id
,a.salary
from (
  select last_name
  ,department_id
  ,salary
  ,rank() over (partition by department_id order by salary desc) as rnk
  from tablename
) a
where rnk=1

The partition groups all employees together who share the same department and should work regardless of the null value.

After grouping them - the order by tells that group to order on salary descending, and give a rank. You can run just the inner query to get an idea of what it does.

Upvotes: 0

Related Questions