Using complex expressions in "Order By" in Mysql

Question

I have a deal table

+---------+---------+ | deal_id | expired | +---------+---------+ | 1 | 0 | | 2 | 0 | | 3 | 0 | | 4 | 0 | | 5 | 1 | | 6 | 0 | | 7 | 1 | | 8 | 1 | | 9 | 0 | | 10 | 0 | +---------+---------+ 

I would like to archive the following order:

1) Expired offers below

2) A deal with deal_id higher than 5 at the top, ordered by deal_id

3) Transactions with an ID below / equal to 5 at the bottom, ordered by RAND with sowing

---

SELECT deal_id, expired FROM ORDER deal by expired = 1, deal_id <5, rand (1) desc

This query is incorrect, since the top of the table will also be sorted by rand, and the top, which I would like to order at deal_id desc.

---

Here's how to keep it:

 +---------+---------+ | deal_id | expired | +---------+---------+ | 10| 0 | top part ordered by | 9 | 0 | deal_id desc | 6 |_______0 | if ( deal_id < 5 AND expired = 0 ) | 4 | 0 | | 1 | 0 | bottom part ordered | 5 | 0 | by rand(seed) | 7 | 0 | expired = 1 at the bottom | 5 | 1 | | 7 | 1 | | 8 | 1 | +---------+---------+ 

Is it possible to archive this using only expressions in "ORDER BY"? I know that I could use UNION, but I really don't want to . This will allow me to keep things within my framework.

Thanks.

Answer 1

Something like that?

 SELECT deal_id, expired FROM Deal ORDER BY expired = 1, deal_id < 5, case when (expired=1 or deal_id<5)=false then deal_id else rand() end desc 

See the fiddle here .

Answer 2

you can use the CASE statement

 ORDER BY CASE WHEN expired = 1 THEN 99 WHEN deal_id < 5 THEN deal_id WHEN deal_id > 5 THEN Rand() + 5 //so that the value is between 5 and 6 ELSE 100 END