Sorting a complex query

Question

MySQL

For example, there is a mega-request:

SELECT  `good` . * , recovery_min, recovery_max, 

@price := IF( price_c <>0, price_c, IF( mid_price <>0, mid_price, 0 ) ) AS con_price,
@color := IF( @price <>0, IF( @price < price_d,  "red", IF( @price <= price,  "yellow",  "green" ) ) ,  "white" ) AS color, 

TRUNCATE( IF( @color =  "red", recovery_min, IF( @color =  "yellow", IF( (
@price - @price * 0.002
) / price_d < recovery_min, recovery_min, (
@price - @price * 0.002
) / price_d ) , IF( @color =  "green", (
@price - @price * 0.002
) / price_d, recovery_max ) ) ) , 4 ) AS result,

FROM  `goods` AS  `good` 

JOIN  `categories` ON (  `categories`.`cat_id` =  `good`.`cat_id` ) 

WHERE  `good`.`cat_id` =  '11'

ORDER BY FIELD(  `color` ,  "red",  "yellow",  "green",  "white" ) ASC 

Why might sorting by colors not work?

Clarification: the result without ORDER BY FIELD is the same, that is, this line is not taken into account at all.

Also, at the initial formation of the request, sorting worked.

Another clarification:

@color := IF( @price <>0, IF( @price < price_d,  "red", IF( @price <= price,  "yellow",  "green" ) ) ,  "white" ) AS color

If you do not use @price here, then it sorts normally :(

Answer 1

You haven't written a DBMS, so it's a bit more difficult to advise you, but you can try this:
ORDER BY CASE
WHEN color = "red" THEN 1
WHEN color = "yellow" THEN 2
ELSE 3 END

Answer 2

Doesn't work because you don't specify which table these fields are in.