MySQL get the row with smallest column value for each product group

Question

I have tables products and product_prices. Like that;

products:
+-------------+----------+
| products_id | title    |
+-------------+----------+
|           1 | phone    |
|           2 | computer |
|           3 | keyboard |
+-------------+----------+


product_prices:
+-------------------+-----------+-------+-------------+
| product_prices_id | productid | price | minquantity |
+-------------------+-----------+-------+-------------+
|                 1 |         1 |   500 |           1 |
|                 2 |         1 |   450 |           2 |
|                 3 |         2 |   800 |           1 |
|                 4 |         2 |   700 |           2 |
|                 5 |         3 |    15 |           1 |
|                 6 |         3 |    10 |           3 |
|                 7 |         3 |     7 |          10 |
+-------------------+-----------+-------+-------------+

So there's multiple prices depending on quantity.

My SQL query is like this:

SELECT
   * 
FROM
   products product 
   INNER JOIN
      product_prices price 
      ON price.productid = product.products_id 
GROUP BY
   product.products_id 
ORDER BY
   price.price;

I'm getting this error:

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'price.product_prices_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The result without GROUP BY is:

+-------------+----------+-------------------+-----------+-------+-------------+
| products_id | title    | product_prices_id | productid | price | minquantity |
+-------------+----------+-------------------+-----------+-------+-------------+
|           3 | keyboard |                 7 |         3 |     7 |          10 |
|           3 | keyboard |                 6 |         3 |    10 |           3 |
|           3 | keyboard |                 5 |         3 |    15 |           1 |
|           1 | phone    |                 2 |         1 |   450 |           2 |
|           1 | phone    |                 1 |         1 |   500 |           1 |
|           2 | computer |                 4 |         2 |   700 |           2 |
|           2 | computer |                 3 |         2 |   800 |           1 |
+-------------+----------+-------------------+-----------+-------+-------------+

What I want to do is, get the row with the cheapest price, grouped by products_id;

+-------------+----------+-------------------+-----------+-------+-------------+
| products_id | title    | product_prices_id | productid | price | minquantity |
+-------------+----------+-------------------+-----------+-------+-------------+
|           3 | keyboard |                 7 |         3 |     7 |          10 |
|           1 | phone    |                 2 |         1 |   450 |           2 |
|           2 | computer |                 4 |         2 |   700 |           2 |
+-------------+----------+-------------------+-----------+-------+-------------+

I think I need to use MIN() but I have tried several things, which did not work. The closest I could do was ordering it by price, limiting to 1, but it was returning 1 product only.
Any ideas?

If it helps, here's the dump for example database I used: https://transfer.sh/dTvY4/test.sql

score 1 · Accepted Answer · answered Jul 10 '19 at 17:26

You need first to find out what are the minimum prices for each product. For that you use the MIN-aggregate function. As you are selecting a normal columnn with aggregate function, you need to list the normal column in the GROUP BY-clause.

Once you know the minimum prices for each product, you just select those rows from the join of the two tables:

select 
 p.products_id, 
 p.title, 
 pr.product_prices_id, 
 pr.productid, 
 pr.price, 
 pr.minquantity
from product_prices pr
  join products p on p.products_id=pr.productid
  join (
    select productid, min(price) as minprice
    from product_prices
    group by productid
  ) mpr on mpr.productid=pr.productid and mpr.minprice=pr.price

See SQLFiddle.

In your query you try to use GROUP BY-clause without an aggregate function, hence the error. Also, you are missing the MIN-logic.

Instead of linking a file to the question, you better create a SQLFiddle / db-fiddle for it. This way it is far easier to answer the question.

MySQL get the row with smallest column value for each product group

1 Answers1