I mapped the high, medium, low and critical statuses to numeric values and sorted on that numeric value
select a.* from (
select 'A' metric ,84 value,'High' as status union
select 'B',10,'Low' union
select 'C',6,'Critical' union
select 'D',40,'Medium'
) a
join (
select 10 ste, 'High' as status union
select 20 ste, 'Medium' as status union
select 30 ste, 'Low' as status union
select 40 ste, 'Critical' as status
) ste on a.status = ste.status
order by ste.ste asc