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