Using Complex Expressions in "Order By" in Mysql

Question

I have a table "Deal"

+---------+---------+
| 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 Deals at the bottom

2) Deals with deal_id higher then 5 at the top, ordered by deal_id

3) Deals with id lower/equal 5 at the bottom ordered by RAND with seed

---

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

This query is wrong as the top of the table will be also ordered by rand, and the top part I would like to order by deal_id desc.

---

This is how it should look after:

+---------+---------+
| 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 that by using just expressions in "ORDER BY"? I know I could use UNION, but I really don't want to, It will let me keep things simple in my framework.

Thanks.

Answer 1

Something like this?

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

Please see fiddle here.

Answer 2

you could use a 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