MySQL Order by numeric sequence

Question

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?

you alwqays have A value in begining ?

echo_Me
– echo_Me

2014-03-11 22:06:31 +00:00
Commented Mar 11, 2014 at 22:06 — echo_Me
– echo_Me, Commented Mar 11, 2014 at 22:06
no, not always, however the others are B and C

skyman
– skyman

2014-03-12 02:49:16 +00:00
Commented Mar 12, 2014 at 2:49 — skyman
– skyman, Commented Mar 12, 2014 at 2:49
ok look my edited answer.

echo_Me
– echo_Me

2014-03-12 08:29:09 +00:00
Commented Mar 12, 2014 at 8:29 — echo_Me
– echo_Me, Commented Mar 12, 2014 at 8:29

Gordon Linoff · Accepted Answer · 2014-03-11 22:06:28Z

2

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

order by length(location), location

answered Mar 11, 2014 at 22:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dognose Over a year ago

taking length into account may work for the example given - But if there is a location like A001 it will fail. However, the provided examples do not clearly mention all possible variations.

echo_Me · Accepted Answer · 2014-03-12 08:28:43Z

1

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

edited Mar 12, 2014 at 8:28

answered Mar 11, 2014 at 22:11

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

1 Comment

skyman Over a year ago

This has been really helpful. In the end the following worked perfectly: ORDER BY SUBSTR(Loc, 1,1), CAST(SUBSTR(Loc, 2, 2) as signed )

Mihai · Accepted Answer · 2014-03-11 22:07:14Z

1

Try

ORDER BY SUBSTR(location, 2)

answered Mar 11, 2014 at 22:07

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

1 Comment

Mihai Over a year ago

@echo_Me I`ve read in my horoscope that as soon as I hit 7000,my salary will be doubled,my girlfriend will be twice as beautiful and North Korea will renounce nuclear weapons.So it will be a very important event,no doubt.

Collectives™ on Stack Overflow

MySQL Order by numeric sequence

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related