|
|
Posted by IchBin on 08/04/06 19:32
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-)
Navigation:
[Reply to this message]
|