CODEWITHSUNDEEP
phpmysqlsql-order-by
melati
melati

Reputation: 167

Order by numeric values in SQL ascending

I'm trying to get this output.

MDT 1
MDT 2
MDT 3
MDT 11
MDT 44

but, The values are ordered alphabetically, so 123 comes before 2.

example :

MDT 1
MDT 11
MDT 156
MDT 2
MDT 3
MDT 303
MDT 44

and so on.

I'm use this code, but it seem didn't work.

SELECT * FROM file ORDER BY ABS(ID) ASC

How can I solve this?

Upvotes: 9

Views: 7672

Answers (5)

WebLook Services
WebLook Services

Reputation: 9

I was searching it out too, but just while reading over here I got it striked in my head and found one solution that if that column is having only numbers as data then you need to make modification in database table and define that column as INT value type, then I am sure what you need will be done. Eg. {SELECT * FROM file ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC} in such case Convert is column and that needs to be defined as INT(5) will work.

Upvotes: 0

Gowri Naidu R
Gowri Naidu R

Reputation: 2034

Try Like this it will sort based on numeric :

select substr(id,4)*1 from file order by substr(id,4)*1

It will gives

1
2
3
11
44
...

If You want all fields try the below query ("substr(id,4)" or "substr(id,5)") based on your string length (ex: id= proj-001911 --> take SUBSTR( id, 6 ) *1) ) 

select * from file order by substr(id,4)*1

Upvotes: 1

Vikdor
Vikdor

Reputation: 24124

If your ID is always going to contain the prefix as MDT, then you can use this, to sort as per your requirement:

SELECT * FROM File 
ORDER BY CAST(replace(ID, 'MDT ', '') AS UNSIGNED) ASC

SQLFiddle demo

Upvotes: 3

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT * FROM file
ORDER BY CONVERT(SUBSTRING(ID,5),UNSIGNED) ASC

SUBSTRING() will extract all characters after 'MDT ' and CONVERT() will change remaining substring to unsigned integer on which ORDER BY is performed

note SUBSTR() is a synonym for SUBSTRING().

Upvotes: 0

Jonathan de M.
Jonathan de M.

Reputation: 9808

Try that snippet 

SELECT * FROM file ORDER BY ID + 0 ASC

Upvotes: 1

Related Questions