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

I would like to develop a query sorted by accepted, and then eventdate so my NULL values will appear at the top of my list in eventdate order. However, if accepted is NOT NULL then I would like the data sorted by the eventdate.

My current query is:

SELECT * FROM invite_view ORDER BY accepted, eventdate

This, however, sorts the table so all accepted = NULL values are first, all 0 values are next, and finally, all 1 values are last, thusly:

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

and I would like something like 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

CodePudding user response：

One way would be using CASE to sort the results conditionally

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

Results:

db<>fiddle here