How can I create a SQL query that uses Col A as the primary sort key, but if Col A is empty, uses Col B as the primary sort key?

Question

Using MariaDB, I have a view that provides information including the date of the event and whether the invitation was accepted. eventdate is an event date of type date. accepted is a field of type tinyint, which is 0 if the invitation is rejected, 1 if the invitation is accepted, otherwise it defaults to NULL.

I want to develop a query that sorts by accepted and eventdate so that my NULL values ​​will appear in eventdate order The front. However, if accepted is not NULL, you want to sort by eventdate.

My current query is:

SELECT * ORDER BY ACCEPTED FROM INVITE VIEW, eventdate

However, this query sorts the table so that all accepted = NULL values ​​come first, all 0 values ​​follow, and finally all 1 values At the end, it looks like this:

eventname | eventdate  | accepted
---------------------------------
Event 1   | 2022-04-14 | NULL
Event 2   | 2022-04-25 | NULL
Event 3   | 2022-03-28 | 0
Event 4   | 2022-05-03 | 0
Event 5   | 2022-04-14 | 1
Event 6   | 2022-05-01 | 1

I hope to get results similar to this:

eventname | eventdate  | accepted
---------------------------------
Event 1   | 2022-04-14 | NULL
Event 2   | 2022-04-25 | NULL
Event 3   | 2022-03-28 | 0
Event 5   | 2022-04-14 | 1
Event 6   | 2022-05-01 | 1
Event 4   | 2022-05-03 | 0

Answer 1

One way is to use the CASE condition to sort the results

SELECT * 
FROM  invite_view 
ORDER BY CASE WHEN accepted IS NULL THEN accepted
              ELSE eventDate
         END

result:

id	eventname	eventdate	accepted
1	Event 1	2022-04-14	null
2	Event 2	2022-04-25	null
3	Event 3	2022-03-28	0
5	Event 5	2022-04-14	1
6	Event 6	2022-05-01	1
4	Event 4	2022-05-03	0