How to sort the list by default, which displays tasks with subtasks, just active tasks and closed ones?

Question

Android

Is it possible to select and sort with one query so that tasks with subtasks are displayed at the top of the list, closed tasks are at the bottom, and between them are active tasks without subtasks?
Or is it better not to complicate things and make three requests, or sort the array that is given to the adapter, or when creating an array for the adapter, first unload tasks into three arrays, which are then glued in the right order?

Answer 1

Generally it is possible. Like this:

SELECT *
FROM tasks
ORDER BY CASE WHEN tasks.status = "active" AND tasks.hasSub = 1 THEN 0 WHEN tasks.status = "active" THEN 1 ELSE 2 END