Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORDER BY alphabet first then follow by number

I looking for some tweak in mysql ordering , I normally select record from table and then order the record by Name(varchar) ASC but the number is always come first

here some example of my question (note. mysql sort the record with 0-9 first)

SELECT name FROM list ORDER BY name ASC
record returned:
1 star
2 star
9 slice
Ape
Age
Beg
Bell
Fish
Zoo

What i want is the alphabet order come first then follow by number

Desired output

Ape
Age
Beg
Bell
Fish
Zoo
1 star
2 star
9 slice
like image 857
Leon Armstrong Avatar asked Jul 02 '13 05:07

Leon Armstrong


2 Answers

Use the following ORDER BY clause:

ORDER BY IF(name RLIKE '^[a-z]', 1, 2), name
like image 73
Barmar Avatar answered Oct 19 '22 06:10

Barmar


Ref this

SELECT name FROM list ORDER BY name * 1 ASC

Edited

SELECT name FROM list ORDER BY name * 1, name ASC
like image 31
Salil Avatar answered Oct 19 '22 05:10

Salil