CODEWITHSUNDEEP
mysqlsorting
wow
wow

Reputation: 573

Sorting varchar field numerically in MySQL

I have a field number of type varchar. Even though it is of type varchar, it stores integer values with optional leading zeros. A sort orders them lexicographically ("42" comes before "9"). How can I order by numeric values ("9" to come before "42")?

Currently I use the query:

SELECT * FROM table ORDER BY number ASC

Upvotes: 48

Views: 74766

Answers (12)

Михаил Юдин
Михаил Юдин

Reputation: 11

Another option to keep numerics at a top, then order by alpha.

IF(name + 0, name + 0, 9999999), name

Upvotes: 0

user9385340
user9385340

Reputation:

MySQL ORDER BY Sorting alphanumeric on correct order

example:

SELECT `alphanumericCol` FROM `tableName` ORDER BY 
  SUBSTR(`alphanumericCol` FROM 1 FOR 1), 
  LPAD(lower(`alphanumericCol`), 10,0) ASC

output:

0
1
2
11
21
100
101
102
104
S-104A
S-105
S-107
S-111

Upvotes: 0

workdreamer
workdreamer

Reputation: 2856

Actually i've found something interesting:

SELECT * FROM mytable ORDER BY LPAD(LOWER(mycol), 10,0) DESC

This allows you to order the field like:

1
2
3
10
A
A1
B2
10A
111

Upvotes: 43

ChAnDu353
ChAnDu353

Reputation: 651

For a table with values like Er353, ER 280, ER 30, ER36 default sort will give ER280 ER30 ER353 ER36

SELECT fieldname, SUBSTRING(fieldname, 1, 2) AS bcd, 
CONVERT(SUBSTRING(fieldname, 3, 9), UNSIGNED INTEGER) AS num 
FROM table_name
ORDER BY bcd, num;

the results will be in this order ER30 ER36 ER280 ER353

Upvotes: 7

Or Weinberger
Or Weinberger

Reputation: 7482

SELECT * FROM table ORDER BY number ASC

Should display what you want it to display.. looks like you're sorting it by id or number is not defined as integer at the moment.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 882686

There are a few ways to do this:

  1. Store them as numeric values rather than strings. You've already discounted that as you want to keep strings like 00100 intact with the leading zeros.
  2. Order by the strings cast as numeric. This will work but be aware that it's a performance killer for decent sized databases. Per-row functions don't really scale well.
  3. Add a third column which is the numeric equivalent of the string and index on that. Then use an insert/update trigger to ensure it's set correctly whenever the string column changes.

Since the vast majority of databases are read far more often than written, this third option above amortises the cost of the calculation (done at insert/update) over all selects. Your selects will be blindingly fast since they use the numeric column to order (and no per-row functions).

Your inserts and updates will be slower but that's the price you pay and, to be honest, it's well worth paying.

The use of the trigger maintains the ACID properties of the table since the two columns are kept in step. And it's a well-known idiom that you can usually trade off space for time in most performance optimisations.

We've used this "trick" in many situations, such as storing lower-cased versions of surnames alongside the originals (instead of using something like tolower), lengths of identifying strings to find all users with 7-character ones (instead of using len) and so on.

Keep in mind that it's okay to revert from third normal form for performance provided you understand (and mitigate) the consequences.

Upvotes: 41

alexii
alexii

Reputation: 271

Trick I just learned. Add '+0' to the varchar field order clause:

SELECT * FROM table ORDER BY number+0 ASC

I now see this answer above. I am wondering if this is typecasting the field and an integer. I have not compared performance. Working great.

Upvotes: 27

minusf
minusf

Reputation: 1373

given a column username containing VARCHAR's like these:

username1
username10
username100

one could do:

SELECT username,
CONVERT(REPLACE(username, 'username', ''), UNSIGNED INTEGER) AS N
FROM users u
WHERE username LIKE 'username%'
ORDER BY N;

it is not cheap, but does the job.

Upvotes: 1

user3816758
user3816758

Reputation: 11

Rough and ready: order by 1*field_name

Upvotes: -3

Rabesh Lal Shrestha
Rabesh Lal Shrestha

Reputation: 304

you can get order by according to your requirement my using following sql query

SELECT * FROM mytable ORDER BY ABS(mycol)

Upvotes: 3

bobs
bobs

Reputation: 22224

Try this

SELECT * FROM table_name ORDER BY CAST(field_name as SIGNED INTEGER) ASC

Upvotes: 89

shantanuo
shantanuo

Reputation: 32220

SELECT * FROM table ORDER BY number + 0

Upvotes: 26

Related Questions