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

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

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
>

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.
>

If you had two products with the same price then which id would you
expect to be returned? You haven't specified, and hence you get both.

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

Try to look at 'max()' function and 'group by'

select max(price), 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-)

ZeldorBlat wrote:
> 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.
>>
>
> If you had two products with the same price then which id would you
> expect to be returned? You haven't specified, and hence you get both.
>

Have not tried this but this may do work without getting complicated:

select distinct max(price), 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-)

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-)

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-)

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.

On Fri, 04 Aug 2006 19:07:28 -0400, IchBin <weconsul [at] ptd.net> wrote:

>Did you try putting the id in the GROUP BY clause.

It sounds like he wants to return only one id per distinct name,
though we have yet to learn the rule for picking which one.

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)