MySQL 8.0 Order by condition

I want to take data from products table. I have to sort by price, but in such a way that first of all it shows those whose price is between the given maximum and minimum.

Example of table:

If min = 10 and max = 15

The result should be this sequence: Prod 1, Prod 4, Prod 5, Prod 3, Prod 2, Prod 6, Prod 7

Thanks.

>Solution :

You can sort by the boolean expression price BETWEEN 10 AND 15 DESC first and then by price:

SELECT *
FROM tablename
ORDER BY price BETWEEN 10 AND 15 DESC, price;

See the demo.