GROUP BY и ORDER BY

SELECT
    products.product_id, products.url, products.category_id, products.brand_id, products.model, products.description, products.body, products.hit, products.show_menu, products.order_num, products.small_image, products.large_image, DATE_FORMAT(products.created_at, '%Y-%m-%d') as created, DATE_FORMAT(products.updated_at, '%Y-%m-%d') as  modified, products.enabled, products.pricelist_comment as pricedescr, products.pre_order_days, products.rating, products.votes, products.header,
    brands.name as brand, brands.url as brand_url,
    categories.single_name as category, categories.name as category_name, categories.url as category_url, categories.image as category_image,
    pv.price,
    pv.variant_id,
    pv.stock,
    pv.sku,
    pv.old_price,
    pi.price as install_price,
    pi.install_id as install_id,
    pi.city_id as install_city_id,
    pi.ot as install_ot,
    pi.type as install_type,
    media.name as media_image,
    media.id as media_id
FROM products
         LEFT JOIN categories ON categories.category_id = products.category_id
         LEFT JOIN brands ON products.brand_id = brands.brand_id
         LEFT JOIN products_categories ON products.product_id = products_categories.product_id
         LEFT JOIN products_variants as pv ON products.product_id = pv.product_id
         LEFT JOIN products_installs as pi ON products.product_id = pi.product_id
         LEFT JOIN media ON products.product_id = model_id AND model_type = 'App\\Models\\Catalog\\Product' AND media.collection_name = 'main_image'
WHERE
        categories.enabled = 1
  AND products.enabled = 1
  AND (pv.city_id = 0 OR pv.city_id = 0 OR pv.city_id IS NULL) AND
    (pi.city_id = 0 OR pi.city_id = 0 OR pi.city_id IS NULL)
  AND ( (categories.category_id in(149) ) OR (products_categories.category_id in(149) ) )
GROUP BY products.product_id
ORDER BY
    CASE WHEN pv.stock = 0 AND products.pre_order_days = 0 THEN 1 ELSE 0 END,
    CASE WHEN (pv.price = 0 OR pv.price IS NULL) AND pi.price = 0 THEN 1 ELSE 0 END, products.order_num
LIMIT 0, 24;

SELECT field1 * (SELECT MAX(x.field1) FROM a_table AS x), ...
  FROM a_table
...