Sort records based on "status" field value (running, upcoming, closed) and the records should come based on date in mysql

Question

I have a requirement to sort the records based on "status" field value( running, upcoming, closed ) order and one more condition is like it should be based on date.

suppose 20-08-2021 and 21-08-2021 is having 7 records like below

title	date	time	status
R1	20-08-2021	08:00:00	closed
R2	20-08-2021	12:00:00	upcoming
R3	20-08-2021	16:00:00	running
R4	21-08-2021	9:00:00	running
R5	21-08-2021	15:00:00	closed
R6	21-08-2021	19:00:00	upcoming
R7	21-08-2021	14:00:00	closed

the results should be like

title	date	time	status
R3	20-08-2021	16:00:00	running
R2	20-08-2021	12:00:00	upcoming
R1	20-08-2021	08:00:00	closed
R4	21-08-2021	9:00:00	running
R6	21-08-2021	19:00:00	upcoming
R7	21-08-2021	14:00:00	closed
R5	21-08-2021	15:00:00	closed

note: here there is a provision to start any event of future dates so we can have future dates events has running and also R7 should come first as its start time is less than R5.

I have used order by case but is there any effective way to crack it easily and order by case also not solving it properly

Answer 1

First use ROW_NUMBER() window function to rank the rows of each status and then check the value of each status:

SELECT *
FROM tablename
ORDER BY ROW_NUMBER() OVER (PARTITION BY status ORDER BY date, time), 
         status = 'running' DESC,
         status = 'upcoming' DESC,
         status = 'closed' DESC;

If your version of MySql does not support window functions use a correlated subquery:

SELECT t1.*
FROM tablename t1
ORDER BY (
           SELECT COUNT(*) 
           FROM tablename t2 
           WHERE t2.status = t1.status 
             AND CONCAT(t2.date, t2.time) <= CONCAT(t1.date, t1.time)
         ), 
         status = 'running' DESC,
         status = 'upcoming' DESC,
         status = 'closed' DESC;

See the demo.