We have loads of variants for our products and we are using distinct on a custom key to display only one and then let the user configure it afterwoods on the product page.
Each of these variants can carry a different price depending on the options.
Using distinct we are able to display the right variation of the product in the search results.
But we would like to display the starting price as the price range for the variations can be quite high and discouraging.
Currently, on search result page we have:
Product A - $ 100
If we filter on color using green value, we have:
Product B - $ 1000
What we would like to have:
Product A - starting from $ 100
Using green filter:
Product B - starting from $ 500
Is there a way to get the min value of a numeric value from a distinct result set?
From an SQL point of view I would write it:
SELECT name, min(price) from products group by key;