Forum Stats

  • 3,733,981 Users
  • 2,246,853 Discussions
  • 7,856,964 Comments

Discussions

Max and Min salaries along with Employee Name

User_X9S6M
User_X9S6M Member Posts: 34 Red Ribbon

SELECT MAX(ENAME) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) ENAME,DEPTNO,MAX(SAL) AS HIGH_SAL FROM SCOTT.EMP GROUP BY DEPTNO /* Max salary from each Dept */


SELECT DEPTNO,MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL,ENAME) AS LEAST_SAL,MIN(ENAME)KEEP(DENSE_RANK FIRST ORDER BY SAL,ENAME) EMP_LEAST_SAL FROM SCOTT.EMP GROUP BY DEPTNO /* Least salary from each dept */

Can we achieve by combine these two above two queries to get desired output as

DEPTNO | HIGH_SAL | ENAME | LOW_SAL | EMP_WITH LEAST_SAL

Best Answers

  • Paulzip
    Paulzip Member Posts: 8,318 Blue Diamond
    edited February 25 Accepted Answer

    Yes, you mean like this?..

    select  deptno
        , max(sal)
        , max(ename) keep (dense_rank first order by sal desc)  ename
        , min(sal) keep (dense_rank first order by sal, ename)   as least_sal
        , min(ename) keep (dense_rank first order by sal, ename)  emp_least_sal
    from   scott.emp
    group by deptno;
    
    Frank KulashAB Samy
  • mathguy
    mathguy Member Posts: 9,773 Gold Crown
    Accepted Answer

    Should that be "EMPLOYEE NAME(S)" rather than simply "EMPLOYEE NAME"?

    The highest salary, 3000, in department 20, is being paid to two different employees - not only to SCOTT, which you show in your output, but also to FORD. FORD (highest salary in dept 20) doesn't appear anywhere in your output, is that OK? I understand how this might be OK for a classroom exercise, but I doubt that it would be fine for a real-life business use.

    Cheers - mathguy

  • mathguy
    mathguy Member Posts: 9,773 Gold Crown
    Accepted Answer

    Here is a query that would address the question I asked earlier. Of course, in a real-life problem you wouldn't use ENAME - there may be several employees with the same name in the same department, with different salaries; you would use employee id or similar. And, the query depends on the fact that there aren't too many employees tied for highest or for lowest salary in a department (so you don't exceed the string length in LISTAGG).

    select deptno
         , max(sal) as high_sal
         , listagg(case flag when 'max' then ename end, ', ') within group (order by ename) as high_sal_empl
         , min(sal) as low_sal
         , listagg(case flag when 'min' then ename end, ', ') within group (order by ename) as low_sal_empl
    from   (
             select deptno, ename, sal,
                    case sal when min(sal) over (partition by deptno) then 'min'
                             when max(sal) over (partition by deptno) then 'max' end as flag
             from   scott.emp
           )
    where  flag is not null
    group  by deptno
    ;
    
    DEPTNO    HIGH_SAL  HIGH_SAL_EMPL        LOW_SAL  LOW_SAL_EMPL   
    ------  ----------  ----------------  ----------  ----------------
        10        5000  KING                    1300  MILLER         
        20        3000  FORD, SCOTT              800  SMITH          
        30        2850  BLAKE                    950  JAMES 
    
    User_X9S6M

Answers

  • Paulzip
    Paulzip Member Posts: 8,318 Blue Diamond
    edited February 25 Accepted Answer

    Yes, you mean like this?..

    select  deptno
        , max(sal)
        , max(ename) keep (dense_rank first order by sal desc)  ename
        , min(sal) keep (dense_rank first order by sal, ename)   as least_sal
        , min(ename) keep (dense_rank first order by sal, ename)  emp_least_sal
    from   scott.emp
    group by deptno;
    
    Frank KulashAB Samy
  • mathguy
    mathguy Member Posts: 9,773 Gold Crown
    Accepted Answer

    Should that be "EMPLOYEE NAME(S)" rather than simply "EMPLOYEE NAME"?

    The highest salary, 3000, in department 20, is being paid to two different employees - not only to SCOTT, which you show in your output, but also to FORD. FORD (highest salary in dept 20) doesn't appear anywhere in your output, is that OK? I understand how this might be OK for a classroom exercise, but I doubt that it would be fine for a real-life business use.

    Cheers - mathguy

  • mathguy
    mathguy Member Posts: 9,773 Gold Crown
    Accepted Answer

    Here is a query that would address the question I asked earlier. Of course, in a real-life problem you wouldn't use ENAME - there may be several employees with the same name in the same department, with different salaries; you would use employee id or similar. And, the query depends on the fact that there aren't too many employees tied for highest or for lowest salary in a department (so you don't exceed the string length in LISTAGG).

    select deptno
         , max(sal) as high_sal
         , listagg(case flag when 'max' then ename end, ', ') within group (order by ename) as high_sal_empl
         , min(sal) as low_sal
         , listagg(case flag when 'min' then ename end, ', ') within group (order by ename) as low_sal_empl
    from   (
             select deptno, ename, sal,
                    case sal when min(sal) over (partition by deptno) then 'min'
                             when max(sal) over (partition by deptno) then 'max' end as flag
             from   scott.emp
           )
    where  flag is not null
    group  by deptno
    ;
    
    DEPTNO    HIGH_SAL  HIGH_SAL_EMPL        LOW_SAL  LOW_SAL_EMPL   
    ------  ----------  ----------------  ----------  ----------------
        10        5000  KING                    1300  MILLER         
        20        3000  FORD, SCOTT              800  SMITH          
        30        2850  BLAKE                    950  JAMES 
    
    User_X9S6M
  • User_X9S6M
    User_X9S6M Member Posts: 34 Red Ribbon

    Thanks All! Now it makes sense I got my desired result with your inputs.

Sign In or Register to comment.