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, Nov 16 '15 at 21:21
Updated the original question to better describe what I'm trying to achieve. — Shamus May, Nov 17 '15 at 16:33

score 2 · Answer 1 · answered Nov 16 '15 at 21:22

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 '15 at 21:22

Will

2,790
19
22

When I run the bottom query it returns the min price, but the Name and Product seem to be from a different row. – Shamus May Nov 16 '15 at 21:37
@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. – Will Nov 16 '15 at 21:42
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](http://sqlfiddle.com/#!9/c8937c/3/0) – Shamus May Nov 16 '15 at 22:25
I know I need to group by Product. The lowest price returns, but the name associated with that record is not right. – Shamus May Nov 16 '15 at 22:28
You also need to group by Name like the above example. Updated [fiddle](http://sqlfiddle.com/#!9/c8937c/2) – Will Nov 16 '15 at 22:40
I only want 1 row per Product (sku) with the lowest price AND all the associated fields of that lowest priced Product (sku). First output would be ---- 11, Merch2, 19.00 then 12, Merch1, 20.00 – Shamus May Nov 16 '15 at 22:48
And you do not care which Merchant name is returned if there are multiple Merchants with the same min price? – Will Nov 16 '15 at 22:54
Preferably I would show the Merchant with the highest rank in the 'Position' field (not shown in the example) if there are multiple merchants with the same min price. – Shamus May Nov 16 '15 at 23:02
I've been working from this example (http://stackoverflow.com/questions/1799355/mysql-group-by-to-return-a-the-min-value-and-get-the-corresponding-row-data?rq=1) and it works, except for the case when merchants have the same price and it displays both rows. Any thoughts how to show the row data based on that 'Position' ranking field I have? – Shamus May Nov 16 '15 at 23:19
This should help: http://stackoverflow.com/questions/8748986/get-records-with-highest-smallest-whatever-per-group – Will Nov 16 '15 at 23:21
Played around all night and still can't get it working. I updated the original question to better describe what I'm trying to achieve. – Shamus May Nov 17 '15 at 16:49

score 0 · Answer 2 · answered Nov 17 '15 at 18:26

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;

MySQL group by min() and limit output

2 Answers2