|
|
Posted by IchBin on 08/04/06 23:07
Skafa wrote:
> 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-)
Sound familiar.. but I have used a lot of DBMS.
I just ran something like this query and had no problem. I am running in
Windows XP SP 2 MySQL 5.0. OK, Did you try putting the id in the GROUP
BY clause.
You should learn how to post to a newsgroup...
That is, do not top post. Makes a thread hard to read since all comments
are out of sync.
Navigation:
[Reply to this message]
|