Mix ascending and descending sorting in conditional sorting in MySQL

Question

I have one table for two different kind of news. I want to create a list of them ordered by a date which depends of the type of news, so I tried this:

SELECT * FROM my_table
ORDER BY case type
              when 'news' then creation_date 
              else 'events' then starting_date
         end desc;

What I want is to sort news by creation_date ASC and events by starting_date DESC. I tried to simply add

when 'news' then creation_date ASC
else 'events' then starting_date DESC

but it doesn't work. Is it possible to do this?

Thank you.

Answer 1

I think you need to do two separate queries, each sorted its own way. To sort, it needs to be able to compare any item to any other item, and decide which comes first. It has no way to compare a 'news' item to an 'event' item and get a meaningful result.

Answer 2

I have found the solution:

SELECT 
    UNIX_TIMESTAMP(creation_date) AS order_column,
    creation_date,
    type,
    other_columns
FROM my_table 
WHERE type='news'
UNION ALL
SELECT 
    -UNIX_TIMESTAMP(starting_date) AS order_column,
    starting_date, 
    type,
    other_columns
FROM my_table 
WHERE type='events' 
ORDER BY order_column

Important is the minus before second UNIX_TIMESTAMP function. It will result with the events sorted descending and then all news sorted ascending. I hope this is what you wanted.

Answer 3

will something like this work?

select m.*, 
       case type
          when 'news' then 
             creation_date 
          when 'events' then 
             starting_date
       end as sort_date
from my_table m
order by sort_date desc

Answer 4

select m.*, 
       case type
          when 'news' then 
             creation_date 
       end as sort_date_1,
       case type
          when 'events' then 
             starting_date
       end as sort_date_2
from my_table m
order by type desc, sort_date_1 asc nulls last, sort_date_2 desc nulls last