[OT] MySQL Prob

[keiran]

I've got a little problem with my MySQL query for sorting my products. The query is simple "ORDER BY itemcode ASC". Problem is it is only sorting to the first 3 digits so for example when it should display like this EA185, EA333, EA1300, EA1900 it displays EA1300, EA185, EA1900.

Is there another way to get them to sort properly? I've noticed `Sage` (accounts program) does the same thing.

Keiran

[thisnameistaken]

You're sorting on a character order, those aren't numeric keys. So of course "1800" comes before "300", because 1 is higher up the order than "3".

If you want to sort numerically, you'll need a numeric column.

Edit: Maybe this (http://drewish.com/tags/mysql) hack would work.

[the_angry_angel]

Because you're probably using a varchar field, or something similiar, it's not being sorted in what's known as "natural order". The problem here is that in MySQL there's no way to order naturally.

Couple of options -
1. Spit everything out and then order in your actual language (i.e. in PHP it should be natcasesort)
2. Do something mad in the SQL statment which chops off the 'EA' from the field, and converts it into a numeric field (something like "SELECT * FROM yourtable ORDER BY CAST(SUBSTRING(yourfield, 3) AS INTEGER) ASC;" might well work (note; untested))

Edit: Damn you kev! :p

[keiran]

Ah, never thought on that!

I'll just remove the EA and add another table containing supplier info.

Cheers guys! :)