user622325 user622325 - 8 months ago 49
MySQL Question

mysql sorting by 2 columns dependencies

I'm searching for a trick, how to sort this:
I have 2 columns: status (numeric) and modification_date (date).

if status<9 (means for me if case is not closed) - it should be "order by status,modification_date

if status=9 (if case is closed) - it should be sorted "order by status,modification_date DESC

So - if I have cases not closed - they should be first in chronological order, but if they are closed - they should be in reversed order (newest first)

I hoped, that I can do UNION, but it's not possible to use "order by" in both sections (or maybe I'm doing something wrong)

Is it possible to do?

ORDER BY status,
         IF(status < 9, 1, -1) * modification_date