MySQL group by min() and limit output

Question

This is the current SQL query I am working with:

SELECT Merchant.Product, Merchant.Name, Merchant.Price
FROM a_table AS Merchant
JOIN
(
SELECT Product, MIN(Price) AS MinPrice
FROM a_table
GROUP BY Product
) AS Price
ON Merchant.Product = Price.Product
AND Merchant.Price = Price.MinPrice

From this data set:

Product    Name         Price   
11          Merch1      19.00   
11          Merch2      20.00
11          Merch3      19.00
11          Merch4      19.50
12          Merch1      20.00   
12          Merch2      20.00
13          Merch1      17.00
13          Merch3      15.00

The current SQL outputs multiple product records when prices are the same like this:

Product    Name         Price   
11          Merch1      19.00
11          Merch3      19.00   
12          Merch1      20.00
12          Merch2      20.00   
13          Merch3      15.00

I want to Group By product and display the lowest price with corresponding row data. If two prices are the same on a product, use first record found.

Trying to get this result:

Product    Name         Price   
11          Merch1      19.00   
12          Merch1      20.00   
13          Merch3      15.00

just eliminate the join entirely. select merchant.*, min(price) ... group product. you'll end up with whatever merchant mysql encountered first, but you will only get one single lowest price record for each product id. — Marc B
– Marc B, Commented Nov 16, 2015 at 21:21
Updated the original question to better describe what I'm trying to achieve. — Shamus May
– Shamus May, Commented Nov 17, 2015 at 16:33

Will · Accepted Answer · 2015-11-16 21:22:26Z

2

You don't need any joins to do this.

If you are looking to get the min price for every product by merchant you can do this:

SELECT Product, Name, MIN(Price) as MinPrice
FROM a_table
GROUP BY Product, Name

If you just want the min price of a product regardless of merchant you can do this:

SELECT Product, MIN(Price) as MinPrice
FROM a_table
GROUP BY Product

answered Nov 16, 2015 at 21:22

Will

2,89022 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Shamus May Over a year ago

When I run the bottom query it returns the min price, but the Name and Product seem to be from a different row.

Will Over a year ago

@ShamusMay, are you running the top (1st) query? The bottom one should only return Product, not Name. The top query will GROUP by Product and Name and then give you the min price for all rows that have that Product and Name.

Shamus May Over a year ago

I'm sure this is me just not explaining what the end result should be correctly. Need to return rows with lowest price per product(sku). Neither of these queries seems to do that. I've tried both queries link

Shamus May Over a year ago

I know I need to group by Product. The lowest price returns, but the name associated with that record is not right.

Will Over a year ago

You also need to group by Name like the above example. Updated fiddle

|

Shamus May · Accepted Answer · 2015-11-17 18:26:58Z

0

Here is the query that finally worked for my needs...

http://sqlfiddle.com/#!9/c8937c/35/0

SELECT emp2.product,
       emp1.name,
       emp2.MinPrice
FROM (
  SELECT product,
     Min(price) as MinPrice
  FROM   Merchant
 GROUP BY product
) as emp2 JOIN Merchant as emp1 ON emp1.price = emp2.MinPrice
GROUP BY product;

answered Nov 17, 2015 at 18:26

Shamus May

358 bronze badges

Collectives™ on Stack Overflow

MySQL group by min() and limit output

2 Answers 2

11 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Linked

Related