problem with Subquery

hi,

I've following table:
+----+-------------+----------+-------+-------+
| id | productName | provider | items | price |
+----+-------------+----------+-------+-------+
| 1 | product x | gmt | 3 | 10 |
| 2 | product x | gnt | 0 | 8 |
| 3 | product x | gat | 7 | 9 |
| 4 | product y | gat | 4 | 10 |
| 5 | product y | gnt | 2 | 7 |
+----+-------------+----------+-------+-------+

I've products from diffrent delivers and I'd like to write the query
which tell me for each product which price is lowest (and have some
items on store - items>0).
So I'd like to have the query which return me the following result:

+----+-------------+----------+-------+-------+
| id | productName | provider | items | price |
+----+-------------+----------+-------+-------+
| 3 | product x | gat | 7 | 9 |
| 5 | product y | gnt | 2 | 7 |
+----+-------------+----------+-------+-------+

I've used quite a lots of combinations group by, subquery, min... but
without success. Please, have you got any idea?

Regards,
Michal

Re: problem with Subquery

mfr wrote:
> hi,
>
> I've following table:
> +----+-------------+----------+-------+-------+
> | id | productName | provider | items | price |
> +----+-------------+----------+-------+-------+
> | 1 | product x | gmt | 3 | 10 |
> | 2 | product x | gnt | 0 | 8 |
> | 3 | product x | gat | 7 | 9 |
> | 4 | product y | gat | 4 | 10 |
> | 5 | product y | gnt | 2 | 7 |
> +----+-------------+----------+-------+-------+
>
> I've products from diffrent delivers and I'd like to write the query
> which tell me for each product which price is lowest (and have some
> items on store - items>0).
> So I'd like to have the query which return me the following result:
>
> +----+-------------+----------+-------+-------+
> | id | productName | provider | items | price |
> +----+-------------+----------+-------+-------+
> | 3 | product x | gat | 7 | 9 |
> | 5 | product y | gnt | 2 | 7 |
> +----+-------------+----------+-------+-------+
>
> I've used quite a lots of combinations group by, subquery, min... but
> without success. Please, have you got any idea?
>
> Regards,
> Michal

It ain't pretty (by which I mean someone will come along in a second
with a much more elegant solution):

SELECT t1.*
FROM (SELECT * FROM table1 WHERE items > 0) t1
LEFT JOIN (SELECT * FROM table1 WHERE items > 0) t2 ON t1.id <> t2.id
AND t1.productName = t2.productName
AND t1.price > t2.price
WHERE t2.price IS NULL;

Re: problem with Subquery

strawberry wrote:
> mfr wrote:
> > hi,
> >
> > I've following table:
> > +----+-------------+----------+-------+-------+
> > | id | productName | provider | items | price |
> > +----+-------------+----------+-------+-------+
> > | 1 | product x | gmt | 3 | 10 |
> > | 2 | product x | gnt | 0 | 8 |
> > | 3 | product x | gat | 7 | 9 |
> > | 4 | product y | gat | 4 | 10 |
> > | 5 | product y | gnt | 2 | 7 |
> > +----+-------------+----------+-------+-------+
> >
> > I've products from diffrent delivers and I'd like to write the query
> > which tell me for each product which price is lowest (and have some
> > items on store - items>0).
> > So I'd like to have the query which return me the following result:
> >
> > +----+-------------+----------+-------+-------+
> > | id | productName | provider | items | price |
> > +----+-------------+----------+-------+-------+
> > | 3 | product x | gat | 7 | 9 |
> > | 5 | product y | gnt | 2 | 7 |
> > +----+-------------+----------+-------+-------+
> >
> > I've used quite a lots of combinations group by, subquery, min... but
> > without success. Please, have you got any idea?
> >
> > Regards,
> > Michal
>
> It ain't pretty (by which I mean someone will come along in a second
> with a much more elegant solution):
>
> SELECT t1.*
> FROM (SELECT * FROM table1 WHERE items > 0) t1
> LEFT JOIN (SELECT * FROM table1 WHERE items > 0) t2 ON t1.id <> t2.id
> AND t1.productName = t2.productName
> AND t1.price > t2.price
> WHERE t2.price IS NULL;


Hi,

thanx! it's huge, but it's working ;-) Any other ideas how to make it
faster?
I'll test it tomorrow, and let you know then about performance.

Regards,
Michal

Re: problem with Subquery

strawberry wrote:
> It ain't pretty (by which I mean someone will come along in a second
> with a much more elegant solution):
>
> SELECT t1.*
> FROM (SELECT * FROM table1 WHERE items > 0) t1
> LEFT JOIN (SELECT * FROM table1 WHERE items > 0) t2 ON t1.id <> t2.id
> AND t1.productName = t2.productName
> AND t1.price > t2.price
> WHERE t2.price IS NULL;

Here's something that is slightly leaner, has no subqueries, and might
perform a bit better.

SELECT t1.*
FROM tablename AS t1
LEFT JOIN tablename AS t2 ON (t1.productName = t2.productName
AND t2.items > 0 AND t1.price > t2.price)
WHERE t1.items > 0 AND t2.id IS NULL

Make sure to use EXPLAIN to figure out if it's doing table-scans, and
add indexes appropriately to fix that.

Regards,
Bill K.