MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (`title` VARCHAR(2), `date` VARCHAR(10), `time` VARCHAR(8), `status` VARCHAR(8));

INSERT INTO tablename (`title`, `date`, `time`, `status`) VALUES
  ('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');
SELECT *
FROM tablename
ORDER BY ROW_NUMBER() OVER (PARTITION BY status ORDER BY date, time), 
         status = 'running' DESC,
         status = 'upcoming' DESC,
         status = 'closed' DESC


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


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


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

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