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