BOTJr. BOTJr. - 1 year ago 93
MySQL Question

Constraints on group by

I have a products table that contains all the products that can be sold by a seller. It has two columns as primary key;


As product weight varies their price varies. 6 types of product weight are available:
0.5, 1, 1.5, 2, 2.5, 3
. Product code for a product having different weights would be the same.

Sample data:

product_code | product_quantity | weight | quantity
1234 | 6 | 0.5kg | 0
1234 | 6 | 1.0kg | 7
1234 | 6 | 1.5kg | 8

I want to display all products, but only once per product_code.

SELECT * FROM products where group by product_code

Given the above three rows, this query gives me the first row of the three.

However, I want to return rows with quantity greater than zero if they exist, otherwise any row will do.

What modification do I need to make in my query to achieve this?

Here's my table structure:

Table structure

Answer Source

Bearing in mind that you're using a non-standard feature, ie grouping by a subset of the non-aggregated columns, in which case (not guaranteed) the first row is returned for each group...

Do the group by over an ordered rowset that encounters non-zero quantities before zero quantities:

  SELECT * FROM products
  ORDER BY quantity desc
) x
GROUP BY product_code

Note: The implementation you're using behaves this way, but no guarantees are given that future versions will continue to work reliably the way you want them to.

Also, as per the documentation:

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY.

This means that your query won't run on 5.7.5+ unless you actively disable ONLY_FULL_GROUP_BY after installing.