Re: MAX/DISTINCT in one query

From: Jan Nowitzky <nowitzky_at_informatik.uni-jena.de>
Date: Thu, 17 Dec 1998 14:49:25 +0100
Message-ID: <36790BE5.CE271FA5_at_informatik.uni-jena.de>

Amar wrote:

> >Sunder wrote in message ...
> > emp_num , dept, salary
> >
> > I want to select the dept,empl_num and salary of the empl with max
> >salary in each dept.
> >
> >ex
> >
> >emp no dept salary
> >1000 10 1000
> >1001 10 2000
> >1002 20 1500
> >1003 20 1100
> >
> >The output of the query should be
> >
> >emp no dept salary
> >1001 10 2000
> >1002 20 1500.
> >
> >Can anybody tell me how this can be accomplished .
> >
> >Sunder
> >
>
> Hi Sunder,
>
> Was this what u were looking for ?
>
> select * from emp where salary in (select max(salary) from emp group by
> dept) order by dept;
>
> Regards,
> Amar

Hi Amar,

your solution will not works in all cases!

Following situation:

    emp no          dept         salary
    1000               10           1500 (1000 changed to 1500)
    1001               10           2000
    1002               20           1500
    1003              20            1100

the query

    select * from emp where salary in (select max(salary) from emp group by dept) order by dept

will deliver

   emp no          dept         salary
    1000               10           1500  => not correct!
    1001               10           2000
    1002               20           1500

You compare only the salaray. If a salary-value of any tupel is in any other department the maximum salary, so this tupel will build the result of your query. We must compare both, department and salary. We must change your query as follows:

    select * from emp where (dept, salary) in (select dept, max(salary) from emp group by dept) order by dept

result:

  emp no          dept         salary
    1001               10           2000
    1002               20           1500

It will works fine!
Best regards,
Jan Received on Thu Dec 17 1998 - 14:49:25 CET