Using Complex Expressions in "Order By" in Mysql

mysql, sql-order-by

Solution

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.

Problem

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.

Original source