Getting ID when sorting by joined table using group also

Question

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?

Answer 1

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