ORDER BY alphabet first then follow by number

mysql sql sorting sql-order-by

28,851

Solution 1

Use the following ORDER BY clause:

ORDER BY IF(name RLIKE '^[a-z]', 1, 2), name

Solution 2

Ref this

SELECT name FROM list ORDER BY name * 1 ASC

Edited

SELECT name FROM list ORDER BY name * 1, name ASC

Solution 3

You can try something like this:

SELECT 
    name 
FROM 
    list 
ORDER BY 
    IF(name REGEXP '^[0-9]', CONCAT('zz',name),name) ASC

So if your name start with a digit you concatenate 'zz' in the beginning (so that it will be last)

28,851

Author by

Leon Armstrong

Currently working and learning in a small company , interest in php,mysql,css,html,java,android

Updated on September 03, 2021

Comments

Leon Armstrong over 2 years
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
```
Barmar almost 11 years

Won't that make numbers go between v and w?
Grijesh Chauhan almost 11 years

RLIKE or LIKE ? didn't get
Stephan almost 11 years

@Barmar your right... i meant to put the last letter of the alphabet but even so it will not solve all the cases like when you have a name : 'zzabc'
Leon Armstrong almost 11 years

@GrijeshChauhan R stand for regex
Barmar almost 11 years

Which is why my solution is best.
Barmar almost 11 years

That ignores all the letters.
Leon Armstrong almost 11 years

Nice hack too , but i'm working on a large dictionary table , so i'll will stick to @Barmar solution , anyway thanks!
Stephan almost 11 years

yes , we can all agree that the best/cleanest solution was offered by @Barmar
Salil almost 11 years

@barmar:- yes, agree with you
Ravi Dhoriya ツ over 9 years

Wow!! It saved my lot of time. Thank you so much :)
Matthew Zourelias almost 7 years

This worked for me. Barmar's answer for some reason did not
friek108 over 5 years

How do you sort descending? Eg. 9,2,1, Zoo,Fish,Bell?
Barmar over 5 years

Add DESC: ORDER BY IF(name RLIKE '[a-z]'), 1, 2) DESC, name DESC