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.

CodePudding user response：

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.

CodePudding user response：

You can use A CASE HWEN to determine the two groups.

1 for between the price

SELECT * FROM product
ORDER By CASE WHEN `price` BETWEEN 10 AND 15 Then 1 
ELSE 2 END ASC, `price` ASC

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

db<>fiddle here

CodePudding user response：

You can also just simply group 2 selects into one with UNION.

select * from test where value >= 10 and value <= 15
union
select * from test where value < 10 or value > 15

Select Demo