Number sorting - 1,2,3 instead of 1,10,2,3

18 posts by 8 authors in: Forums > CMS Builder
Last Post: November 29, 2010   (RSS)

By rjbathgate - May 21, 2009

Hello...

If i have a record field which goes:

1
2
3
4
5
6
7
8
9
10
11
12

How can i get it to sort numerically correctly?

At moment it's going;

1
10
11
12
2

Etc...

is there an easy way without having to change 1 to 01

cheers

Re: [rjbathgate] Number sorting - 1,2,3 instead of 1,10,2,3

By Dave - May 21, 2009

Hi Rob,

Either change the column type to a numeric one (INT or FLOAT) or add +0 to your orderBy like this:

'orderBy' => "my_number+0"

You can also put that in the section editor. Adding +0 to the orderBy is the easiest.

Let me know if that works for you.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Number sorting - 1,2,3 instead of 1,10,2,3

By rjbathgate - May 21, 2009

Hey Dave,

Cheers;

'orderBy' => "issue+0 DESC",

Worked a treat
Rob

Re: [Dave] Number sorting - 1,2,3 instead of 1,10,2,3

By Codee - September 22, 2010

But what does the field type need to be? Textfield is the only reasonable choice and it doesn't work this way...

Re: [equinox69] Number sorting - 1,2,3 instead of 1,10,2,3

By Chris - September 22, 2010

Hi equinox69,

You can sort on a text field using the +0 trick. Can you explain what you mean by "it doesn't work this way"?
All the best,
Chris

Re: [chris] Number sorting - 1,2,3 instead of 1,10,2,3

By Codee - September 23, 2010

In a section named "Components" I have a field named "number" that is a text field. It's set to only allow the following characters ' 1,0,2,3,4,5,6,7,8,9, '. If I click on the "sorting" tab at the top of the admin page and put the following in to the Order By box:

'orderBy' => "number+0 DESC"

I get the following error:

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=> "number+0 DESC" LIMIT 200' at line 4.

Is the error because I put the full command with the order by clause?

Re: [equinox69] Number sorting - 1,2,3 instead of 1,10,2,3

By zip222 - September 23, 2010

Did you include the comma at the end of that line?

'orderBy' => "number+0 DESC",

Re: [pothompson] Number sorting - 1,2,3 instead of 1,10,2,3

By Codee - September 23, 2010

Thank you. That's what I was asking. The instructions for the section said either should work but it works the way you suggested for sure. thanks!

Re: [chris] Number sorting - 1,2,3 instead of 1,10,2,3

By degreesnorth - October 31, 2010

Hi

Can you clarify how to do a multiple sort. I'm using: 'orderBy' => 'suburb, address+0 DESC',
however, the addresses aren't appearing in correct order - see http://www.fordland.com.au/leasesummary.php ... the suburbs are correct (listed alphabetically), but the address aren't. Any ideas?

Thanks