CODEWITHSUNDEEP
mysqlsql
skyman
skyman

Reputation: 2335

MySQL Order by numeric sequence

I have a "Location" data set returned by a simple query from a MySQL database:

A1
A10,
A2
A3

It is sequenced by an "Order By Location" statement. The issue is that I would like the returned sequence to be:

A1
A2
A3
A10

I am not sure if this is achievable with a MySQL Order By statement?

Upvotes: 0

Views: 162

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

order by CAST(replace((Location),'A','') as signed )

DEMO HERE

EDIT:

if you have other letters then A then consider to cut the first letter and order the rest as integers.

    ORDER BY CAST(SUBSTR(loc, 2) as signed )

DEMO HERE

Upvotes: 1

Mihai
Mihai

Reputation: 26784

Try

ORDER BY SUBSTR(location, 2)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think the easiest way to do this is to order by the length and then the value:

order by length(location), location

Upvotes: 2

Related Questions