CODEWITHSUNDEEP
mysqlgroup-by
h2odev
h2odev

Reputation: 664

Select the entire row of the lowest price for distinct product

I have the below table about products:

| id | name   | product_id | price | seller_id | discount_id |
--------------------------------------------------------------
| 1  | phone  | 11         | 400   | 7         | 19          |
| 2  | cpu    | 78         | 120   | 33        | 4           |
| 3  | phone  | 11         | 380   | 8         | 22          |
| 4  | phone  | 11         | 460   | 5         | 19          |
| 5  | memory | 80         | 45    | 12        | 16          |
| 6  | router | 98         | 115   | 7         | 16          |
| 7  | cpu    | 78         | 115   | 33        | 66          |

I need to select all the columns of distinct product_id with the lowest price. Also to ORDER the result by price ASC. For this example:

| id | name   | product_id | price | seller_id | discount_id |
--------------------------------------------------------------
| 5  | memory | 80         | 45    | 12        | 16          |
| 6  | router | 98         | 115   | 7         | 16          |
| 7  | cpu    | 78         | 115   | 33        | 66          |
| 3  | phone  | 11         | 380   | 8         | 22          |

I have no problems doing this using GROUP BY product_id and min(price) but I also need other columns (seller_id & discount_id)

How can I produce the result above from MySQL?

Upvotes: 1

Views: 354

Answers (2)

dognose
dognose

Reputation: 20889

You can join the table with itself (on product_id). As a join-condition add left.price > right.price - and then choose the rows, where right.price is null, because for that join, there is no lower right price, meaning the one you have left is the lowest:

SELECT l.id, l.name, l.product_id, l.price, l.seller_id, l.discount_id 
FROM 
  products l
LEFT JOIN
  products r
on
  l.product_id = r.product_id AND l.price > r.price
WHERE
  isnull (r.price) -- that means: no cheaper price for this position.

intermediate result (SELECT * no WHERE) would look like (shortened): 

| l.id | l.name    | l.product_id | l.price | r.id | r.name   | r.product_id | r.price
| 3    | phone     | 11           | 380     | null | null     | null         | null 
| 4    | phone     | 11           | 460     | 3    | phone    | 3            | 380

Side node: For very large datasets there might be performance-issues, because every additional line of a component would add multiple result rows. i.e. consider another phone: 

| l.id | l.name    | l.product_id | l.price | r.id | r.name   | r.product_id | r.price
| 3    | phone     | 11           | 380     | null | null     | null         | null 
| 4    | phone     | 11           | 460     | 3    | phone    | 3            | 380
| 5    | phone     | 11           | 500     | 3    | phone    | 3            | 380
| 5    | phone     | 11           | 500     | 4    | phone    | 3            | 460

So, if you want to get the lowest price in the past 60 days with daily changes, that will be a huge amount of rows just for "that"... (Actually 60+59+58+...+2+1 I think, cause the most expensive price will produce 59 comparision rows and so on)

Upvotes: 0

Mihai
Mihai

Reputation: 26784

Add a subquery with the min price and join on min price and product.

SELECT id, name,product_id,price,seller_id,discount_id FROM t
JOIN
(SELECT tt.product_id,MIN(tt.price) minp FROM t as tt 
GROUP BY tt.product_id)x
ON x.product_id=t.product_id AND x.price = t.price

Another option with LIMIT

SELECT * FROM T WHERE EXISTS
(SELECT 1 FROM T as TT ORDER BY TT.price ASC LIMIT 1 
WHERE t.id= TT.id)

Given that the MEMORY engine is so restricting go the caveman way

SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY price),',',1),
SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY price),',',1),
product_id,MIN(price),
SUBSTRING_INDEX(GROUP_CONCAT(seller_id ORDER BY price),',',1),
SUBSTRING_INDEX(GROUP_CONCAT(discount_id ORDER BY price),',',1) FROM t
GROUP BY product_id

Upvotes: 2

Related Questions