Posted by Skafa on 08/04/06 19:59

id is not contained in a aggregrate function :)

IchBin schreef:
> Skafa wrote:
>> that's too obvious :-)
>> i also need to return the id.
>>
>> i found this to be working:
>>
>> select id, naam, prijs
>> from tblproduct p_outer
>> where prijs = (
>> select max(prijs)
>> from tblproduct p_inner
>> group by naam
>> having p_outer.naam = p_inner.naam
>> )
>>
>> altough, when two products have the same price, both are returned.
>>
>>
>> Baj-SGC818 schreef:
>>> Hi Skafa
>>>
>>> Try this
>>>
>>> Select
>>> name , Max(price) from products
>>> group by name
>>>
>>> All the best
>>> Baj-SGC818
>>>
>>>
>>> Skafa wrote:
>>>> For example:
>>>>
>>>> I have a table 'products' with columns id, name and price
>>>>
>>>> In this table there can be duplicate product names:
>>>>
>>>> 1 - product1 - 10
>>>> 2 - product1 - 15
>>>> 3 - product2 - 12
>>>> 4 - product1 - 9
>>>>
>>>> I need a select query which returns unique products (by name) and if it
>>>> finds duplicates, it should return the product with the highest price.
>>>> In this case:
>>>>
>>>> 2 - product1 - 15
>>>> 3 - product2 - 12
>>>>
>>>> how can i do this ?
>>>>
>>>> Remco
>>>
>
> Have not tried this but this should do what you want without getting.
> complicated:
>
> select distinct max(price), id, name from products group by name;
>
> Thanks in Advance...
> IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
> __________________________________________________________________________
>
> 'If there is one, Knowledge is the "Fountain of Youth"'
> -William E. Taylor, Regular Guy (1952-)

[Back to original message]