Sample Header Ad - 728x90

Getting ID when sorting by joined table using group also

1 vote
1 answer
68 views
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?
Asked by kicaj (123 rep)
Jan 31, 2023, 03:51 PM
Last activity: Feb 3, 2023, 07:41 AM