mysql group by to return a the min value and get the corresponding row data

Question

I have a table of data like so :

- PK_table - merchantName - price - Product
- 1        - argos        - 7     - 4
- 2        - comet        - 3     - 4
- 1        - Dixon        - 1     - 3
- 1        - argos        - 10    - 4

I wish to select the minimum price for a product and the corresponding merchant in mysql.

I tried:

SELECT Product, merchantName, min(price)
FROM a_table
GROUP BY product

however the result returned is incorrect since it chooses the first merchant name and not the corresponding merchant of the MIN.

how do you do it?

Here is the same situation/solution with more in the accepted answer (years later, but potentially more helpful to researchers): Get row with highest or lowest value from a GROUP BY — mickmackusa
– mickmackusa ♦, Commented May 10, 2018 at 5:44

Larry Lustig · Accepted Answer · 2009-11-25 19:27:08Z

8

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

Will return two rows if two merchants have the same low, low price.

answered Nov 25, 2009 at 19:27

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

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

2 Comments

Manu Over a year ago

Sorry for bringing up an old thing.. I was wondering if there is a method by which I can achieve this without a subquery. I can do the sub-query because expression inside MIN is complex, time consuming one..

Larry Lustig Over a year ago

If optimized properly, and you want only a single product, this query will only perform the MIN calculation on the rows it needs. If your engine is not optimizing (and calculation MIN(Whatever) for all products), you can optimize manually by changing FROM a_table to FROM a_table WHERE Product = Some_Product. If you want the query for all products, then you need to use the sub-select, but that's okay because all the calculations are needed for the final result.

Collectives™ on Stack Overflow

mysql group by to return a the min value and get the corresponding row data

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related