MySQL 8.0

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

create table t as

select 1 as id, 'Milk ' as name, 'SA' as status, null as subid, 'Box 1' as location union all

select 2 as id, 'MyDay ' as name, 'SA' as status, 1 as subid, null union all

select 3 as id, 'Kitkat ' as name, 'SA' as status, 4 as subid, null union all

select 4 as id, 'Chocolate' as name, 'SA' as status, null as subid, 'Box 2' as location union all

select 5 as id, 'Twix ' as name, 'SA' as status, 4 as subid, null union all

select 6 as id, 'Bounty ' as name, 'SA' as status, 4 as subid, null union all

select 7 as id, 'Milkbox ' as name, 'SA' as status, 1 as subid, null

select t.*

from t

order by coalesce(subid, id),

(case when subid is null then 1 else 2 end);

id	name	status	subid	location
1	Milk	SA	null	Box 1
2	MyDay	SA	1	null
7	Milkbox	SA	1	null
4	Chocolate	SA	null	Box 2
3	Kitkat	SA	4	null
5	Twix	SA	4	null
6	Bounty	SA	4	null