Search code examples
mysqllaravelsql-order-by

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:

Title price
Prod 1 10
Prod 2 8
Prod 3 15
Prod 4 11
Prod 5 12
Prod 6 20
Prod 7 24

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.