sql - MySQL get UNION, order by intersection? -

i'm having bit of conundrum here. i'm baking recipe application , let's have query of "mayonnaise", "bacon" , "cheese".

i'd return recipes containing "mayonnaise" or "bacon" or "cheese" ordered if have "mayonnaise", "bacon" , "cheese" followed have "mayonnaise" , "bacon", etc.

so far have bit cloogey like:

select * recipes    lower(ingredients) '%mayonnaise%'       or lower(ingredients) '%bacon%' 

but that's 1 half of want. how across finish line?

thanks!

you try prioritising presence of ingredients , ordering whether or not ingredient included:

select * recipes  lower(ingredients) '%mayonnaise%'  or    lower(ingredients) '%bacon%' or    lower(ingredients) '%cheese%' order lower(ingredients) '%mayonnaise%' desc,          lower(ingredients) '%bacon%' desc,          lower(ingredients) '%cheese%' desc