1

I have small query to getting products based on variants (colors, materials), like:

SELECT 
  ANY_VALUE(id) AS id,
  parent_id,
  COUNT(id),
  color,
  material,
  MIN(price) AS priceMin
FROM 
  (
    SELECT 
      products.id,
      products.parent_id,
      products.price,
      colors.variant_option_id AS color,
      materials.variant_option_id AS material
    FROM products
    LEFT JOIN products_variant_options AS colors
      ON products.id = colors.product_id
      AND colors.variant_id = 1 # Colors
    LEFT JOIN products_variant_options AS materials 
      ON products.id = materials.product_id
      AND materials.variant_id = 2 # Materials
    WHERE
      products.status = 1
  ) AS product_variants
GROUP BY
  parent_id,
  color,
  material
ORDER BY priceMin

Everything you can see on https://www.db-fiddle.com/f/vfAfRWoo2vHKB6S1phE2dt/1

You see I need get id of product.

When I using MAX/MIN or ANY_VALUE for this, you see it row with selected price not returned correct ID - look product with price=8 return id=2 instead of id=4.

I know the function MAX, MIN and awful ANY_VALUE isn't good idea. I tried ANY_VALUE with OVER() but without effects.

How to sorting by prices from joined tables and getting ID during using group?

7
  • The query was created based on this problem: dba.stackexchange.com/questions/322843/…. Maybe this will be helpful.
    – kicaj
    Commented Jan 31, 2023 at 15:53
  • It work fine here Commented Jan 31, 2023 at 15:59
  • That's a "groupwise-max" problem; see the tag I added. Pluse more Q&A on Stackoverflow.com .
    – Rick James
    Commented Jan 31, 2023 at 21:44
  • @ErgestBasha No, look for next row with price=40 return id=7 instead of id=6
    – kicaj
    Commented Feb 1, 2023 at 10:56
  • 1
    [greatest-n-per-group], aka [groupwise-max] with n=1
    – Rick James
    Commented Feb 1, 2023 at 19:35

1 Answer 1

0

I gave up using it GROUP in favour of using CTE and Window functions.

Maybe it will save someone time: https://www.db-fiddle.com/f/hyhUMMtidU1vxnmCiZXZYk/55

Query:

WITH variant AS (
  SELECT
    pv.id,
    colors.variant_option_id as color,
    materials.variant_option_id as material,
    ROW_NUMBER() over(
      PARTITION BY 
        pv.parent_id,
        colors.variant_option_id,
        materials.variant_option_id
      ORDER BY pv.price ASC
    ) AS listing
  FROM products AS pv
  LEFT JOIN products_variant_options AS colors
    ON colors.product_id = pv.id
    AND colors.variant_id = 1 # Colors
  LEFT JOIN products_variant_options AS materials
    ON materials.product_id = pv.id
    AND materials.variant_id = 2 # Materials
  WHERE 
    pv.status = 1
)
  
SELECT 
  v.id, 
  p.name, 
  p.price
FROM variant AS v 
LEFT JOIN products AS p
  ON p.id = v.id
WHERE 
  v.listing = 1
ORDER BY 
  p.price ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.