Prevent mixing of rows when using GROUP BY?

Question

I have this query for a shipping calculator that is supposed to select out the lowest value from a database based on several criteria such as weight and country. The query is meant to get the first lowest value for one or multiple ShippingProviderIds that match the criteria.

The problem is the group by query mixes the results of several rows from the ShippingCost table and throws hings out of whack. I need Price and ShippingCostId to be from the same row. How can I prevent this mixing of results? Maybe Group By is not what I need?

SELECT MIN(s1.Price), s1.ShippingCostId, s1.Weight, s2.CoD, s2.Artnr, s2.CoD, 
s2.Regular, s2.ShippingProviderId, s3.ProviderName FROM ShippingCost s1

INNER JOIN ShippingProvider s2 ON s1.ShippingProviderId = s2.ShippingProviderId 
INNER JOIN ShippingProviderTranslations s3 ON (s2.ShippingProviderId = s3.ShippingProviderId AND s3.Language = 'xx') 

WHERE CountryId = 123 AND (Weight >= 0.5 AND Weight <= 50) AND s2.Regular = 0

GROUP BY s2.ShippingProviderId

If I remove the MIN() expression the rows do no not mix anymore, but the lowest value is not the one found, because the lowest value it has a higher ShippingCostId and was added later (is primary key). It does not work to add the MIN() expression for weight either, or to try and sort by weight. I've tried a whole number of different approaches but I am feeling stumped.

Any suggestions?

Would you like to add sample data and expected output as text to your question? — P.Salmon, May 25 '18 at 11:44
There are intuitive solutions, but they probably won't make much sense described in a generic way. We really need data and ideally schema information. — William_Wilson, May 25 '18 at 11:46
SQL select only rows with max value on a column question will give you the best overview how to achieve this task (several different approaches), just the example focuses on max(), as opposed to min(). I also provided an exact match that shows min(). — Shadow, May 25 '18 at 12:12

score 1 · Accepted Answer · answered May 25 '18 at 11:50

You shuold use a join on min price and not the select for not grouped by columns

SELECT t1.min_price, s1.ShippingCostId, s1.Weight, s2.CoD, s2.Artnr, s2.CoD, 
s2.Regular, s2.ShippingProviderId, s3.ProviderName 

FROM ShippingCost s1

INNER JOIN ShippingProvider s2 ON s1.ShippingProviderId = s2.ShippingProviderId 
INNER JOIN ShippingProviderTranslations s3 ON (s2.ShippingProviderId = s3.ShippingProviderId AND s3.Language = 'xx') 
INNER JOIN (
  select MIN(s1.Price) as min_price, s2.ShippingProviderId
  from FROM ShippingCost s1 
  INNER JOIN ShippingProvider s2 ON s1.ShippingProviderId = s2.ShippingProviderId 
  group by s2.ShippingProviderId
) t1 on t1.ShippingProviderId = s2.ShippingProviderId and s1.Price= t1.min_price

The use of select for not gruoped by columns with aggregation function produce unpredictable result
this habit is deprecated in SQL and in the most recent version of mysql in not allowd and produce error

This sample code worked out well. I modified it some to add in the critiera for countryId and weights and such and now it returns the proper rows from the tests I've done. Thanks a lot! — Dennis, May 26 '18 at 08:21

Prevent mixing of rows when using GROUP BY?

1 Answers1