Ordering the Dodgy Digits!

Yes, there is actually! :) It's because it's not a numeric field. You can trick MySQL into treating as a numeric field by adding "+0" after the fieldname in the "Order By". So it might look like this: refnum+0 (or in reverse) refnum+0 DESC

Hope that helps! Let me know if that works! :)

Dave -

There is, surely, no limit to your talents!

It works - sort of - in some instances. The trouble is, I forgot to mention that some of the ref codes have letters before the digits. So, for example, one list would be...

G1
G10
G11
G12
...etc.

It doesn't work in this instance - but thanks all the same.

[:)]

AJ

So do you want it to sort by the letter first and then the number? Or to ignore the letter and just sort by the number?

Dave -



Either, both and all of the above. Whichever method works best!

[:)]

AJ

Hmm, I've been playing around with this for a bit with not much luck. The 'Order By' is actually just a MySQL "ORDER BY" clause - part of a MySQL query. So you can use any MySQL functions or tricks in there.

The kind of sorting you want is called "natural sort order", it's how humans expect things to be sorted when you have letters an numbers together. I did some research on google (link) and there is a lot of people talking about it but not many solutions that would work for this case.

MySQL has a list of function you can use (link) but nothing was exactly what we needed.

One idea I came up with was to remove all the characters. If all you had was "R" you could do that with an order by like this and have it sort on the number only:

REPLACE(refnum, 'R', '')+0

Except if you had more letter than just R you'd just to nest it like this (for S as well):

REPLACE(REPLACE(refnum, 'R', ''), 'S', '')+0

And so on. It's workable, but gets pretty complicated pretty quickly when you have lots of possible letters

Another totally different idea would be to had a second field just for sorting where you enter just the number part of the reference num.

Hope that helps. If I think of anything else I'll post.

Dave -



Crikey. You never do things by halves, do you? That's a fabulous answer - and it works too!

The only thing I've noticed is that when you use the code on a list page you need to use double quotes around the data in parentheses...

REPLACE(refnum, "[/#ff0000]R"[/#ff0000], ""[/#ff0000])+0



You can also use the code (with double or single quotes) in the 'Order by' box of the Section Editor screens.

This will be very useful.

Thanks again!

[:)]

AJ

Great! Glad to hear that did the job. :)

Just wanted to follow up on this for other users. If you are having problems with sorting of integers, such as:

1
10
2
3
4
5
6
7
8
9

... instead of the expected ...

1
2
3
4
5
6
7
8
9
10

Then I have a trick for you to use in your viewer code. Use the MySQL CAST function in your ORDER BY expression... like so:

SELECT * FROM `faq` WHERE `num` > 0 ORDER BY CAST(`myorder` AS UNSIGNED) ASC

I find this much easier than trying to pad numbers with zeros. Unless you have gigantic result sets the CPU hit should be very minimal.