select distinct `product_flat`.*,
IF( product_flat.special_price_from IS NOT NULL
    AND product_flat.special_price_to IS NOT NULL ,
    IF( NOW( ) >= product_flat.special_price_from
        AND NOW( ) <= product_flat.special_price_to,
        IF( product_flat.special_price IS NULL
            OR product_flat.special_price = 0 , product_flat.price,
            LEAST( product_flat.special_price, product_flat.price ) ) , product_flat.price ) ,
            IF( product_flat.special_price_from IS NULL ,
                IF( product_flat.special_price_to IS NULL ,
                    IF( product_flat.special_price IS NULL OR product_flat.special_price = 0 , product_flat.price,
                    LEAST( product_flat.special_price, product_flat.price ) ) ,
                    IF( NOW( ) <= product_flat.special_price_to,
                        IF( product_flat.special_price IS NULL
                        OR product_flat.special_price = 0 , product_flat.price,
                        LEAST( product_flat.special_price, product_flat.price ) ) , product_flat.price ) ) ,
                        IF( product_flat.special_price_to IS NULL ,
                            IF( NOW( ) >= product_flat.special_price_from,
                                IF( product_flat.special_price IS NULL
                                OR product_flat.special_price = 0 , product_flat.price,
                                LEAST( product_flat.special_price, product_flat.price ) ) , product_flat.price ) , product_flat.price ) ) ) AS price
from `product_flat`
left join `products` on `product_flat`.`product_id` = `products`.`id`
left join `product_categories` on `products`.`id` = `product_categories`.`product_id`
left join `product_flat` as `flat_variants` on `product_flat`.`id` = `flat_variants`.`parent_id` and `flat_variants`.`channel` = 'default' and `flat_variants`.`locale` = 'ua'
inner join `cars_product_relation` on `cars_product_relation`.`id_product` = `product_flat`.`product_id`
where `product_flat`.`channel` = 'default'
    and `product_flat`.`locale` = 'ua'
    and `product_flat`.`url_key` is not null
    and `product_categories`.`category_id` = 10
    and `product_flat`.`status` = 1
    and `product_flat`.`visible_individually` = 1
    and (`cars_product_relation`.`id_model` = 4340)
group by `product_flat`.`id`
order by product_flat.sort_order IS NULL, product_flat.sort_order+0 asc, `product_flat`.`created_at` desc
limit 9
offset 0