|
|
Posted by Skafa on 08/05/06 10:01
IchBin schreef:
> 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.
MySQL does this VERY wrong. you never know what the value of id will be.
it's completely random data. most other dbms return an error if you try
to select a column in a group by statement, without containing it in an
agregate function or in the group by itself (which in my case isn't
possible, cause i wan't to group by name)
Navigation:
[Reply to this message]
|