Page 1 of 1

Number and Letter Order

Posted: Sun Feb 28, 2010 4:41 am
by espy69
Hi

I hope someone can help. I am having trouble ordering letters and numbers in the same column. I am retrieving information from a mysql database and how I want the output to look is :

1
2
3
upto 20
F
H
Z

If I use ABS() it pushes the letters to the top:

F
H
Z
1
2
3
upto 20
If I take ABS off it puts it in the order of 1,10,11 and so on

Is there anyway either in the mysql query or php that I can output in the correct order

Many thanks

Steve

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 10:15 am
by davex
Does not just ORDER BY fieldname(ASC); work? - I think the numbers would come first in the order.

Cheers,

Dave.

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 11:22 am
by espy69
Hi

Thanks but tried that and it doesnt work as mysql sees 10 as the next number after 1 e.g 1, 10, 11 etc..

Thanks

Steve

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 11:57 am
by davex
Hi,

Ok sorry.

So basically you want it to sort as if it was number base 36 (0-9 then A-Z) so the following data:

0, 10, A, B, AA, A1, 120, ABC

Would come out as:

0, A, B, 10, A1, AA, 120, ABC

In that case I think ORDER BY LENGTH(field) ASC, field ASC

Should work, sorting by length in the first instance and then sorting all of the same length by the field value.

Cheers,

Dave.

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 12:04 pm
by espy69
Hi

Thanks but Im not testing the length of the value, I just want it to return numbers in order i.e 1 to 20 followed by letters.

Many thanks

Steve

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 12:30 pm
by davex
Right - sorry again for not quite getting it.

This is messy as anything but seems to do the job:

ORDER BY testfield REGEXP '^[0-9]*$' DESC,CAST(testfield AS SIGNED INTEGER) ASC, LENGTH(testfield) ASC, testfield ASC

So it...

Orders numeric-only (REGEXP)
Orders those numbers correctly (REGEXP ASC would give the letters first so need to swap the order) (CAST)
Orders by length (so AA comes after B)
Orders by ascending order (so A comes before Z)

So with data of:

0, 10, A, B, AA, 120

This will return the order:

0, 10, 120, A, B, AA

Is that right? Sorry if I've missed it again. If I have can you give an example dataset and how you would like it ordered fully?

Thanks,

Dave.

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 12:37 pm
by espy69
Thanks Dave

Will give that a go and get back to you if I come across any probs

Thanks again Steve

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 12:55 pm
by espy69
Excellent, works like a charm

Many thanks

Steve

Re: Number and Letter Order

Posted: Sun Feb 28, 2010 1:00 pm
by davex
No problem - sorry it took me so long to get there.

And I'm sure there are better ways but can't think of any.

Regards,

Dave.

Re: Number and Letter Order

Posted: Mon Mar 01, 2010 9:58 am
by pickle
Try doing a "natural sort". MySQL has no function for doing that, but I do remember seeing a simple trick that didn't involve regex (which should be avoided if possibly simply due to the overhead it incurs).