CODEWITHSUNDEEP
mysqlsql
Mark C.
Mark C.

Reputation: 408

SQL conditional order by?

I need to be able to sort a query of tickets based first on whether they are open and second by descending order of creation. If I ticket is open it has a completed date of '0000-00-00 00:00:00'. If it is closed it has the correct completed date. So the table has these fields (that are important):

I want any tickets that have a completed date of '0000-00-00 00:00:00' to be at the top in reverse order of when they were created and then all closed tickets to simply be in reverse order below that.

My first attempt was:

select id, message
from tickets
where {{search conditions}}
ORDER BY tickets.created, tickets.id DESC;

But unfortunately, because the tickets.created field is a date time, I get the open tickets on top but the first closed ticket is the oldest ticket returned from the search. Is there some way I can make the order by statement conditional to give me all the open tickets and then give me the rest of the tickets in an id desc sort order?

Upvotes: 0

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use two keys in the order by:

order by (t.completed = '0000-00-00 00:00:00') desc,  -- put these first
         t.completed desc

Upvotes: 2

Related Questions