need oracle query

Question

I am practising Oracle Subqueries.. (I am new to Oracle.)

Question: Find the Highest Earning Employee in each Department?

My query below works (BUT I feel its not that good, even though I get the correct result )

select e.deptid, e.name, e.salary 
from employee e 
where e.salary = (select max(salary) 
                  from employee b 
                  where b.deptid = e.deptid )

Is there another easy way? (Using inner joins or some other way?)

And I also am wondering: When exactly do we have to use Inner joins instead of using SubQueries? When exactly do we have to use SubQueries instead of Inner joins?

your query _doesn't work_ for cases when lots of employees have same salaries — Oleg Mikheev, Apr 14 '13 at 06:53
why it doesn't work when having more employees?, BTW Employee and Department are different tables... Do u know any other way to write query for above prob? — Irwin, Apr 14 '13 at 08:18
This is not a duplicate of the proposed duplicate...it is doing something quite different. — Jonathan Leffler, Apr 15 '13 at 02:08
@JonathanLeffler - okay, how about this one? http://stackoverflow.com/q/1533240/146325 — APC, Apr 15 '13 at 02:15

score 1 · Answer 1 · answered Apr 14 '13 at 06:52

1

Why using JOIN here?

select 
  deptid,
  min(name) keep (dense_rank first order by salary desc),
  max(salary)
from 
  employee 
group by
  deptid

answered Apr 14 '13 at 06:52

Egor Skriptunoff

23,359
2
34
64

i didnt get this: min(name) keep (dense_rank first order by salary desc), BTW, Is there any other way? – Irwin Apr 14 '13 at 08:14

score 0 · Answer 2 · edited Apr 14 '13 at 13:31

0

I have got another query as below:

select dept_id, fname,salary 
from (select dept_id, fname,salary, 
      rank() over (order by salary desc) ranking 
      from department d, employee e 
      where d.dept_id = e.deptid) where ranking=1;

I feel above is correct, BUT i have a doubt in my above query: I didnt used any Joins in my query BUT still iam able to retrive columns from 2 tables.. (so no use of joins?)

edited Apr 14 '13 at 13:31

EvilTeach

28,120
21
85
141

answered Apr 14 '13 at 10:03

Irwin

105
1
2
7

1

You don't use any data from DEPARTMENT so why do you think you need to include it in the query? But anyway, your inner query does have a join, it just uses the oder syntax of the WHERE clause rather than the ANSI `join ... on ` syntax. – APC Apr 15 '13 at 02:08

score 0 · Answer 3 · answered Apr 14 '13 at 13:25

you were close - you'r missing the order by salary in rank

select *
from ( 
    select  dept_id , fname , salary , 
            rank() over (partition by dept_id order by salary) as rnk 
    from    department d, employee e 
    where   d.dept_id = e.deptid
where rnk = 1

need oracle query

3 Answers3