Consulta compleja, varias cosultas a tabla, ordenadas por un campo pero filtradas

[jurena]

Así:
SELECT t1.products_id, t1.products_name, t1.products_quantity, t1.products_price FROM ((SELECT products_id, products_name, products_quantity, products_price FROM `products` WHERE products_quantity = 0)
UNION
(SELECT products_id, products_name, products_quantity, products_price FROM products WHERE products_quantity > 0 order by products_price))t1 LIMIT 10

Algunos nombres de campo los he inventado. Adapta la sintaxis a los nombers de tus campos