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?

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

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