problem with Subquery

[mfr]

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

[strawberry]

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;

[mfr]

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

[Bill Karwin]

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.