Forum Stats

  • 3,781,549 Users
  • 2,254,529 Discussions
  • 7,879,752 Comments

Discussions

Highest salary in each departments ORA-00918 error

User_X9S6M
User_X9S6M Member Posts: 63 Red Ribbon

/* SQL query to find employees who have the highest salary in each of the departments */

Approach 1 :

select emp.ename as employee,

    dept.dname as department, 

sal

from scott.emp inner join scott.dept 

on emp.deptno= dept.deptno

where(deptno, sal) in(select deptno, max(sal)as salary from emp group by deptno);


ORA-00918: column ambiguously defined

---------------------------------------------------------------------------------------------------------

Approach 2 :

/* SQL query to find employees who have the highest salary in each of the departments */


select *from

(

select e.ename, e.sal, d.deptno, rank()over(partition by d.deptno order by e.sal desc) rn from scott.emp e join scott.dept d on e.deptno=d.deptno

) where rn=1


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Approach 3 :

/* SQL query to find employees who have the highest salary in each of the departments */


 select 

e1.deptno,

e1.EName,

e1.Sal

from 

scott.Emp e1 left join scott.Emp e2 on e1.Sal <= e2.Sal

and e1.Deptno = e2.Deptno 

group by 

  e1.Deptno, 

  e1.Sal,

  e1.EName 

having count(distinct e2.Sal) = 1

order by e1.deptno asc

Tagged:

Best Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond
    Accepted Answer

    I would just do...

    SQL> with dept_sal_max as (
      2    select deptno, max(sal) as max_sal from emp group by deptno
      3    )
      4  select emp.ename, dept.dname, emp.sal
      5  from   emp
      6         join dept_sal_max dsm on (emp.sal = dsm.max_sal)
      7         join dept on (dept.deptno = emp.deptno)
      8  /
    
    
    ENAME      DNAME                 SAL
    ---------- -------------- ----------
    KING       ACCOUNTING           5000
    FORD       RESEARCH             3000
    SCOTT      RESEARCH             3000
    BLAKE      SALES                2850
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond
    Accepted Answer

    Which version? For that I'm actually on an old 11.2.0.3 database. My data may have some additional stuff in it from other demo's or testing I've done.

«1

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    In your first query:

    where(deptno, sal) in(select deptno, max(sal)as salary from emp group by deptno);
    

    The "where (deptno, sal)" part doesn't know which deptno you are referring to ... the emp.deptno or the dept.deptno? You need to be explicit.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond
    Accepted Answer

    I would just do...

    SQL> with dept_sal_max as (
      2    select deptno, max(sal) as max_sal from emp group by deptno
      3    )
      4  select emp.ename, dept.dname, emp.sal
      5  from   emp
      6         join dept_sal_max dsm on (emp.sal = dsm.max_sal)
      7         join dept on (dept.deptno = emp.deptno)
      8  /
    
    
    ENAME      DNAME                 SAL
    ---------- -------------- ----------
    KING       ACCOUNTING           5000
    FORD       RESEARCH             3000
    SCOTT      RESEARCH             3000
    BLAKE      SALES                2850
    


  • User_X9S6M
    User_X9S6M Member Posts: 63 Red Ribbon

    @BluShadow I corrected it as per your suggestion and worked fine. By the way, in which version you tested the query?


    select e.ename as employee,

    d.dname as department, sal

    from scott.emp e inner join scott.dept d

    on e.deptno= d.deptno

    where(d.deptno, sal) in(select deptno, max(sal)as salary from scott.emp group by deptno);

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    If you're going to use ranking, then use dense_rank to group ties...

    with r as (
      select emp.ename
            ,dept.dname
            ,emp.sal
            ,dense_rank() over (partition by emp.deptno order by sal desc) as rnk
      from emp join dept on (emp.deptno = dept.deptno)
      )
    select ename, dname, sal
    from   r
    where  rnk = 1
    /
    
    ENAME      DNAME                 SAL
    ---------- -------------- ----------
    KING       ACCOUNTING           5000
    SCOTT      RESEARCH             3000
    FORD       RESEARCH             3000
    BLAKE      SALES                2850
    


    And you could also use "dense_rank keep first", but that will only give you the first record when there are ties, so it depends what you actually want...

    select max(emp.ename) keep (dense_rank first order by sal desc) as ename
          ,max(dept.dname) keep (dense_rank first order by sal desc) as dname
          ,max(emp.sal) keep (dense_rank first order by sal desc) as sal
    from   emp join dept on (emp.deptno = dept.deptno)
    group by emp.deptno
    /
    
    ENAME      DNAME                 SAL
    ---------- -------------- ----------
    KING       ACCOUNTING           5000
    SCOTT      RESEARCH             3000
    BLAKE      SALES                2850
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond
    Accepted Answer

    Which version? For that I'm actually on an old 11.2.0.3 database. My data may have some additional stuff in it from other demo's or testing I've done.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond

    Actually I missed a bit from the answer you've accepted...

    It should be...

    with dept_sal_max as (
      select deptno, max(sal) as max_sal from emp group by deptno
      )
    select emp.ename, dept.dname, emp.sal
    from   emp
           join dept_sal_max dsm on (emp.deptno = dsm.deptno and emp.sal = dsm.max_sal)
           join dept on (dept.deptno = emp.deptno)
    

    I forgot to include the deptno in the join condition for the max salary, which of course would be needed if there are same salaries in different departments.

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    @BluShadow

    If you're going to use ranking, then use dense_rank to group ties...

    Is there a difference between dense_rank() over()=1 and rank() over()=1?

  • BluShadow
    BluShadow Member, Moderator Posts: 41,615 Red Diamond


    No, when comparing for the top 1, then it makes no difference. However, if the requirement should change to the top-N (where N > 1) then it will make a difference. So rather than have to go and change any rank() to dense_rank() to accommodate a change of requirements, why not use dense_rank() in the first place to already cater for ties.

    I tend to opt for dense_rank by default and would only use rank if there was a specific reason to do so.

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    However, if the requirement should change to the top-N (where N > 1) then it will make a difference.

    Top-N requirements may vary. For example, oracle fetch first with ties implements requirements using the rank() function.