Re: MAX/DISTINCT in one query

Thanks Jan for the correction.

Jan Nowitzky wrote in message <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 - 18:25:32 CET