|
|
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
>
Navigation:
[Reply to this message]
|