Mysql - Catch minor record in a 1:N ratio

Question

Good evening, I may have this answer somewhere but I spent all day researching, using various examples and nothing.

I have 2 tables, one of products and another with the models being that 1 product can have N models, each model has its price, so the same product can have N different values depending on the model.

With this, I need to search the 2 tables picking only 1 product and 1 model, this model being the one with the lowest price. First I did it:

SELECT `produto`.`titulo`, `modelo`.`sku`, `modelo`.`preco_de`, `modelo`.`preco_por` FROM `produto`
INNER JOIN `modelo` ON `modelo`.`codigo_produto` = `produto`.`codigo`
ORDER BY `modelo`.`preco_por` ASC LIMIT 0, 20

Here he took the product and all models, for example, a refrigerator has 2 models, 110v and 220v, then listed the refrigerator 2 times, one with the price of 110v and the other with the price of 220v, with that I changed the code to:

SELECT `produto`.`titulo`, `modelo`.`sku`, `modelo`.`preco_de`, `modelo`.`preco_por` FROM `produto`
INNER JOIN `modelo` ON `modelo`.`codigo_produto` = `produto`.`codigo`
GROUP BY `produto`.`codigo`
ORDER BY `modelo`.`preco_por` ASC LIMIT 0, 20

Blz, now I have 1 record per product, but now ORDER BY has been ignored because GROUP BY always takes the first record.

I need to do this to always list the product with your model that has the lowest price but I don’t know what else to do, I’m almost giving up and searching only the product and then doing 20 searches to get the lowest price of each one, for me this is a defeat and I didn’t want to do, but I’ve already missed a whole day of the project that’s already pretty tight.

Edited 01: Using the MIN(modelo.preco_por) AS preco_por indicated by the friend below, he really picks the lowest price but keeps getting the record that comes first in the bank.

Edited 02: I’m using Mariadb 10.5.8 which is the newest version in the Docker repository.

Answer 1

Good morning,

André, from what I’ve seen if you’re using Mysql 8.0.14 or later you can use a Side derived table:

SELECT 
  produto.titulo, 
  mod.sku, 
  mod.preco_de, 
  mod.preco_por 
FROM produto,
LATERAL
(
  SELECT
    modelo.sku, 
    modelo.preco_de, 
    modelo.preco_por 
  FROM modelo
  WHERE
    modelo.codigo_produto = produto.codigo
  ORDER BY 
    modelo.preco_por ASC 
  LIMIT 1
) mod

Edit: follows below another suggestion using the row_number function to number the rows in ascending price order

SELECT 
  produto.titulo, 
  mod.sku, 
  mod.preco_de, 
  mod.preco_por 
FROM produto
INNER JOIN 
(
  SELECT
    codigo_produto,
    sku,
    preco_de, 
    preco_por,
    row_number() over(partition by codigo_produto order by preco_por) as row_num
  FROM modelo
) mod ON mod.codigo_produto = produto.codigo
WHERE
  mod.row_num = 1

I hope it helps

Answer 2

You will get what you want using the grouping function MIN together with the clause GROUP BY:

SELECT `produto`.`titulo`, MIN(`modelo`.`preco`) FROM `produto`
INNER JOIN `modelo` ON `modelo`.`codigo_produto` = `produto`.`codigo`
GROUP BY `produto`.`titulo`