[Solved] Sorting query by importance, weight, and length

by


I find this problem rather challenging in MySQL. I can think of a few different approaches:

  • union all
  • variables
  • window function (not available in MySQL prior to 8.0)
  • subqueries in the order by clause

None of these are trivial. Here is one solution:

select t.*
from ((select t.*, 1 as which
       from t
       where status="important"
       order by height
       limit 3
      ) union all
      (select t.*, 2 as which
       from t
       where status="important"
       order by height
       limit 999999 offset 2
      )
     ) t
order by which,
         (case when which = 1 then weight end),
        weight, desc, length desc

solved Sorting query by importance, weight, and length