SELECT * from product
ORDER BY
    CASE
        WHEN NOT ISNULL(promotion_price) and is_active = 1 THEN 1
        WHEN ISNULL(promotion_price) and is_active = 1 THEN 2
        WHEN ISNULL(promotion_price) and is_active = 0 THEN 3
        WHEN NOT ISNULL(promotion_price) and is_active = 0 THEN 4
    END ASC