mysql sort alphabetical and number

Question

I have an array that I want to sort alphabetically but also by the number at the end.

"SELECT DISTINCT Number FROM database WHERE 1 Order By Number ASC";

Here is how it currently sorts:

Number 1
Number 10
Number 11
Number 2
Number 3
Number 4
Number 5
Number 6
Number 7
Number 8
Number 9
The End

This is how I want it to sort:

Number 1
Number 2
Number 3
Number 4
Number 5
Number 6
Number 7
Number 8
Number 9
Number 10
Number 11
The End

Looks like your number column is a string value. You could try casting it to an integer before ordering it — Taryn East, May 06 '13 at 06:55
possible duplicate of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) — Ejaz, May 06 '13 at 12:04
Possible duplicate of [SQL order string as number](https://stackoverflow.com/questions/11808573/sql-order-string-as-number) — But those new buttons though.., Aug 30 '17 at 22:09

score 8 · Answer 1 · answered May 06 '13 at 06:56

8

Add another sort condition:

Order By LENGTH(Number), Number;

This works because a longer number is also a bigger number; for numbers of the same length, you can make a textual comparison, because '0' < '1' .... < '9'

answered May 06 '13 at 06:56

Ja͢ck

170,779
38
263
309

Is this going to work always, 'cause I really feel like there could be some hidden catch. – Menno May 06 '13 at 06:59
@Aquillo I've added an explanation why it works. Feel free to find a case where it gives the wrong results :) – Ja͢ck May 06 '13 at 07:03
Since Taryn made a comment on this for another answer, what if the preceding string (in this case 'Number') is not always 7 chars? Your length would be messed up. – Menno May 06 '13 at 09:41
@Aquillo This logically still assumes the prefix is the same, even though the length itself is not fixed; if I knew more, I could suggest using `SUBSTRING_INDEX()` to sort left first, then right. But that might fail for `The End`. – Ja͢ck May 06 '13 at 09:47
You're right, noticed it too. But your solution would put `The end` up front too, since the length is smaller? (Still think you got a nice approach there, though feel like this is a bad database design) – Menno May 06 '13 at 09:52
1

@Aquillo Yeah, it doesn't help that natural sorting is pretty much an absent feature in MySQL :( – Ja͢ck May 06 '13 at 09:56
This fails for me with this data: `Version 1.2, Version 1.3, Version 1.8, Version 1.9, Version 1.10, Version 3.0, Version 3.3`. Using this solution results in the longer number coming last which is incorrect. The longest number, `1.10`, should come after `1.9` and before `3.0`. – Martin James Jun 25 '18 at 15:21

score 2 · Answer 2 · answered May 06 '13 at 07:19

2

Try this :-

SELECT distinct numberr FROM tablename Order By cast(substring(numberr,7) as unsigned int) ASC ;

Its working fine.

Out put :-

Number 1
Number 2
Number 3
Number 4
Number 5
Number 6
Number 7
Number 10
Number 11

answered May 06 '13 at 07:19

JDGuide

6,239
12
46
64

It's not sorting alphabetically. – Ja͢ck May 06 '13 at 07:22
1

This will only work if the string at the beginning is always 7 chars long... you can't guarantee that. – Taryn East May 06 '13 at 07:24

score 0 · Answer 3 · answered May 06 '13 at 07:02

0

sql has functions to cast a string to an integer while it's sorting.

If you are using mysql, this is what you'd use to do what you want:

SELECT DISTINCT Number FROM database Order By CAST(Number AS UNSIGNED) ASC

If you are using a different database, you will need to google how to cast a column to an integer for your database.

Note: some of the other solutions work... but are kind of hacky - ie they look cool, but might not work in future. The above does exactly what you want and is how you are "supposed" to do it ;)

answered May 06 '13 at 07:02

Taryn East

27,486
9
86
108

`SELECT CAST('Number 11' AS UNSIGNED)` yields `0`. – Ja͢ck May 06 '13 at 07:04
Why are you passing it a string with the word number in it? Don't pass "Number 11", pass "11" or the column named `Number` – Taryn East May 06 '13 at 07:19
Try: `SELECT CAST('11' AS UNSIGNED)` and you correctly get `11` – Taryn East May 06 '13 at 07:20
1

I'm passing that string because that's what's inside the `Number` column :) – Ja͢ck May 06 '13 at 07:21
Aha - yes, I see. I missed the part where it said it has a string and then a number... – Taryn East May 06 '13 at 07:22

Amir · Answer 4 · 2013-05-06T11:25:21.387

0

"SELECT DISTINCT Number FROM database WHERE 1 Order By substring_index(Number,'Number',1),cast(substring_index(Number,'Number ',-1) as unsigned int) ASC";

edited May 06 '13 at 11:25

answered May 06 '13 at 07:31

Amir

4,089
4
16
28

What would happen in case of 'The end'? – Menno May 06 '13 at 09:55
@Aquillo, do you mean `the end` instead of `Number` string? – Amir May 06 '13 at 10:37
Instead of 'Number 9' for example, as in the provided example. – Menno May 06 '13 at 11:01

score 0 · Answer 5 · edited Nov 24 '20 at 06:55

I'm much late but I faced the same issue. My answer could help those who are facing.
You can use SUBSTRING_INDEX function to solve this issue. Like your number is at the end of the string. Your query should be.

SELECT DISTINCT Number 
FROM database 
WHERE 1 Order By 0+SUBSTRING_INDEX(Number,' ',-1) 
ASC;

what this will do is it will take the last chunk of the text separated by space. and the 0+ will force to convert it to integer.

score 0 · Answer 6 · answered May 29 '22 at 22:07

0

try adding "+0" to the sort field:

SELECT DISTINCT Number FROM database WHERE 1 Order By Number+0 ASC

answered May 29 '22 at 22:07

Alexander Goryachev

1

mysql sort alphabetical and number

6 Answers6