CODEWITHSUNDEEP
mysql
user1282355
user1282355

Reputation: 143

mysql sort alphabetical and number

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

Upvotes: 6

Views: 4936

Answers (6)

Alexander Goryachev
Alexander Goryachev

Reputation: 1

try adding "+0" to the sort field:

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

Upvotes: 0

Kamran Ali Raza Sandrana
Kamran Ali Raza Sandrana

Reputation: 13

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.

Upvotes: 0

Amir
Amir

Reputation: 4111

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

Upvotes: 0

JDGuide
JDGuide

Reputation: 6525

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

Upvotes: 2

Taryn East
Taryn East

Reputation: 27747

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 ;)

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173522

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'

Upvotes: 8

Related Questions