Mysql sort data from my table like described

Question

My table looks like this, (let's just say it has no primary or any other key)

I want data to be in this format

help please :(

Answer 1

SELECT a.* FROM css_01 a LEFT JOIN (SELECT substring_index(name, '_', 2) as meta_like_key, value FROM css_01 wpm1 WHERE name like "company_%_name" ) b ON substring_index(a.name, '_', 2) = b.meta_like_key ORDER BY b.value ASC ,substring_index(a.name,'_',2) asc,a.name desc

Answer 2

Using string functions you can nearly achieve that using following

select a.*
from demo a
join (select value,substring_index(name,'_',2) company_name 
      from demo 
      where substring_index(name,'_',-1) = 'name'
) b on substring_index(a.name,'_',2) = b.company_name
order by b.value asc,substring_index(a.name,'_',2) asc,substring_index(name,'_',-1) desc 

Demo