Posted by Skafa on 08/04/06 16:39

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
>

[Back to original message]