CODEWITHSUNDEEP
mysql
Dev Webgile
Dev Webgile

Reputation: 33

Order by alphanumeric in mysql

Can you help me?

my query is 

SELECT num FROM sortnum ORDER BY lpad(num, 10, 0)

result of this query is not good here is the result 

1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
1a 
2a 
2b 
A1 
A2 
A3 
A4 
B1 
A10 
A11 
B10

What is the best query for this type of data i want number first, then data starting with a , then with b

i want result like 

1 
1a 
2 
2a 
2b 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
A1 
A2 
A3 
A4 
A10 
A11 
B1 
B10

Thanks in advance

Upvotes: 2

Views: 2148

Answers (2)

eggyal
eggyal

Reputation: 125835

Assuming that any alphabetic prefix will be at most one character in length, you could do:

SELECT   num
FROM     sortnum
ORDER BY
  CAST(num AS UNSIGNED)=0,     -- those starting with numbers before non-numbers
  CAST(num AS UNSIGNED),       -- then by number prefix (if any)
  LEFT(num,1),                 -- then by first character
  CAST(MID(num,2) AS UNSIGNED) -- then by remaining numbers

See it on sqlfiddle.

Upvotes: 6

Salil
Salil

Reputation: 47472

SELECT num,
CONVERT(SUBSTRING_INDEX(num,'-',-1),UNSIGNED INTEGER) AS num
FROM sortnum
ORDER BY num;

Upvotes: 0

Related Questions