MySQL Is Ordering All Rows Except One

Question

I am trying to retrieve project numbers from a database in ascending order.

The rows look something like this:

-- project number --
       1199.7
       1053.2
       1020
       1023-D
       1023-C
       1023-B
       50
       34.3T
       870-W

Here is my query for retrieving and sorting the data:

$this->db->select('Project');
$this->db->from('`active_projects`');
$this->db->order_by('Project + 0, Project');

Output for part of the table:

My question is, why is 127 stuck in the middle and not at the top? I'm thinking that it is looking at it the same as 1270 just without the appending 0 but I'm not sure. Can anybody help me out in fixing it?

Do a search for "mysql natural order", there are a few solutions — rjdown, Dec 03 '15 at 21:32

Nick · Answer 1 · 2015-12-03T21:45:04.110

2

Because your data type is string and ordered in alphabet order.

Use order_by('cast(Project as UNSIGNED), Project');

edited Dec 03 '15 at 21:45

answered Dec 03 '15 at 21:31

Nick

9,735
7
59
89

So, how would I fix it? – Dec 03 '15 at 21:32
Same output as before. – Dec 03 '15 at 21:38
@Nik, try `UNSIGNED` instead of `decimal` – Nick Dec 03 '15 at 21:45
Just updated to using `UNSIGNED`, everything is in correct order except the 127. Not sure why it won't move.. – Dec 03 '15 at 21:46

score 0 · Answer 2 · answered Dec 03 '15 at 21:43

0

You can use the natsort() function from php.

$array = $result_from_query;

$sorted_array = natsort($array);

Now just return the sorted array.

answered Dec 03 '15 at 21:43

nestedl00p

490
3
14

MySQL Is Ordering All Rows Except One

2 Answers2