MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (`Category` VARCHAR(13), `OrderNum` INTEGER);

INSERT INTO tablename (`Category`, `OrderNum`) VALUES
  ('Prerequisites', '2'),
  ('NULL', '4'),
  ('Prerequisites', '6'),
  ('Sign Off', '8'),
  ('Sign Off', '10');
SELECT * FROM tablename
ORDER BY CASE 
           WHEN Category = 'Prerequisites' THEN 1
           WHEN Category IS NULL THEN 2
           ELSE 3
         END, 
         Category,
         OrderNum;


Category
OrderNum


Prerequisites
2

Prerequisites
6

NULL
4

Sign Off
8

Sign Off
10


SELECT * FROM tablename
ORDER BY Category = 'Prerequisites' DESC, 
         Category IS NULL DESC, 
         Category,
         OrderNum;


Category
OrderNum


Prerequisites
2

Prerequisites
6

NULL
4

Sign Off
8

Sign Off
10


SELECT t1.* FROM tablename t1
ORDER BY (SELECT MIN(t2.OrderNum) FROM tablename t2 WHERE t2.Category = t1.Category),
         t1.OrderNum;


Category
OrderNum


Prerequisites
2

Prerequisites
6

NULL
4

Sign Off
8

Sign Off
10


SELECT * FROM tablename
ORDER BY MIN(OrderNum) OVER (PARTITION BY Category),
         OrderNum;


Category
OrderNum


Prerequisites
2

Prerequisites
6

NULL
4

Sign Off
8

Sign Off
10