MySQL query where the order by value has an exception

Question

I have a table that contains a bunch of values for various "trouble ticket" parts, most specifically one called "TicketStatus" and of course one called "DateCreated". I am struggling to write a query that displays all my tickets in date descending order, but with the COMPLETED tickets listed after those with other status (new/pending/assigned/inprogress, etc).

If I ignore the TicketStatus then my query is:

SELECT * FROM tickets ORDER BY `DateCreated` DESC;

I could run two queries back to back and put them together afterwords:

SELECT * FROM tickets WHERE `TicketStatus`!= 'Completed' ORDER BY `DateCreated` DESC;

SELECT * FROM tickets WHERE `TicketStatus`= 'Completed' ORDER BY `DateCreated` DESC;

But I am confident there is a way to do some magic in my ORDER BY clause to simply "sink" the "completed" ones to the bottom? I tried: SELECT * FROM tickets ORDER BY `TicketStatus`='Completed', `DateCreated` DESC; but that definitely does not work.

Thanks!

Answer 1

I think, this union should do what you're asking for. Basically, add a literal value column that identifies each portion of the data

SELECT incomp.*, 1 specialOrder 
FROM tickets incomp 
WHERE `TicketStatus`!= 'Completed' 

UNION ALL

SELECT comp.*, 2 specialOrder 
FROM tickets comp
WHERE `TicketStatus`= 'Completed'
ORDER BY specialOrder, `DateCreated` DESC;

oh, you're using same table.. Can do this

SELECT
  *
FROM
  tickets
ORDER BY
  (
    CASE WHEN TicketStatus = 'Completed' THEN 2 ELSE 1 END
  ),
  DateCreated DESC;

TEST

create table T (a int, b varchar(20), c datetime);
insert into T values (1, 'aaa', now(3) + 1);
insert into T values (2, 'bbb', now(3) + 2);
insert into T values (3, 'aaa', now(3) + 3);
insert into T values (4, 'bbb', now(3) + 4);
insert into T values (5, 'aaa', now(3) + 5);
insert into T values (6, 'bbb', now(3) + 6);

SELECT *
FROM
  T
ORDER BY
  (
    CASE WHEN b = 'aaa' THEN 2 ELSE 1 END
  ),
  c DESC;

--a     b       c
--6     bbb     2024-08-14 01:34:11
--4     bbb     2024-08-14 01:34:09
--2     bbb     2024-08-14 01:34:07
--5     aaa     2024-08-14 01:34:10
--3     aaa     2024-08-14 01:34:08
--1     aaa     2024-08-14 01:34:06