select-query out of my league

sp0rk

Here's something I can't quite wrap my head around. I'm using MySQL 4.0.5.

Suppose I have a table like this...

company | product | price | comment
-----------------------------------
a       |   apple |     6 | nonsense
a       |  orange |     5 | foo
b       |   apple |     3 | bar
b       |  orange |     5 | blah

I want to select the one row per company that has the lowest price, regardless of anything else. Ie. the two rows in the middle are the ones I'm after.

Yes, I do indeed want to compare apples to oranges. 🙂

Lars_Berg

I can't see any way to do this with one query with Mysql.

Mysql solution:

create table temp(compay varchar(10),price decimal(10,2))

insert into temp 
select company,min(price) from t 
group by company

select t.* from t natural join temp

SQL solution:

select * from t t1
where price in (
  select min(price) from t
    where company = t1.company)

sp0rk

I had a feeling that would be the answer. Where, oh, where is MySQL 4.1 and subqueries...

Temporary table it it, then. Thanks for your help.

PHPdev

There are solutions for sub queries in the MySQL manual... I think they use Joins and some other solutions.

PHPdev