SQL sort string with numbers certain pattern first

Question

I have a table that has a column (cat_name). Some are strings followed by numbers and others are just plain strings. I like to arrange it by putting all strings starting with 'Level' first.

Desired output:

• Level 1 Items
• Level 2 Items
• Level 3 Items
• Level 5 Items
• Level 10 Items
• Level 12 Items
• Level 22 Items
• Apple
• Mango
• Others
• Special Items

I used this query

SELECT * FROM category ORDER BY
(CASE WHEN cat_name LIKE 'Level%' THEN 0
ELSE 1
END) ASC, cat_name

And got

• Level 1 Items
• Level 10 Items
• Level 12 Items
• Level 2 Items
• Level 22 Items
• Level 3 Items
• Level 5 Items
• Apple
• Mango
• Others
• Special Items

And found this query here at stackoverflow for natural sorting

SELECT * FROM category WHERE cat_name LIKE 'Level%' ORDER BY LEFT(cat_name,LOCATE(' ',cat_name)), CAST(SUBSTRING(cat_name,LOCATE(' ',cat_name)+1) AS SIGNED), cat_name ASC

but I don't know how I can integrate it with my first query. The closest I could get is

SELECT * FROM category ORDER BY LEFT(cat_name,LOCATE(' ',cat_name)), CAST(SUBSTRING(cat_name,LOCATE(' ',cat_name)+1) AS SIGNED),
(CASE WHEN cat_name LIKE 'Level%' THEN 0
ELSE 1
END) ASC, cat_name ASC

But the strings with Levels is off. It is arranged numerically but they are not occupying the top position.

• Apple
• Mango
• Others
• Level 1 Items
• Level 2 Items
• Level 3 Items
• Level 5 Items
• Level 10 Items
• Level 12 Items
• Level 22 Items
• Special Items

I think I am just missing something here. Hope someone can help me. Thanks in advance!

sqlfiddle: http://sqlfiddle.com/#!2/5a3eb/2

Answer 1

Try with:

SELECT * FROM category 
ORDER BY (CASE WHEN cat_name LIKE 'Level%' THEN 0
ELSE 1
END)ASC,
LEFT(cat_name,LOCATE(' ',cat_name)), CAST(SUBSTRING(cat_name,LOCATE(' ',cat_name)+1) AS SIGNED),
  cat_name ASC

It's to do with the order of statements within your ORDER BY