CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
Leon Armstrong
Leon Armstrong

Reputation: 1303

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

Upvotes: 15

Views: 36442

Answers (5)

Surbhit Rao
Surbhit Rao

Reputation: 685

This is what worked for me in Rails with a POSTGRESQL DB

.order(Arel.sql("SUBSTRING(LOWER(name), '^[A-Za-z].*'), SUBSTRING(LOWER(name), '^[0-9]+')::FLOAT"))

Upvotes: 0

Salil
Salil

Reputation: 47472

Ref this

SELECT name FROM list ORDER BY name * 1 ASC

Edited

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

Upvotes: 11

Ganesh Rengarajan
Ganesh Rengarajan

Reputation: 2006

Try this..

It simple one to get your answer

SELECT name  from list ORDER BY (name +0) ASC ,name ASC

Upvotes: -2

Barmar
Barmar

Reputation: 780688

Use the following ORDER BY clause:

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

Upvotes: 34

Stephan
Stephan

Reputation: 8090

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)

Upvotes: 0

Related Questions