Consider, if you will, the following product codes:
That would be that 'natural' order of things as far as a human is concerned.
MySQL would order them thusly:
I can understand that logic, but how can I convince MySQL to come round to my way of thinking?
ORDER BY 0+SUBSTRING(productcode FROM 2)
it's a hack, but it works
Thanks for your reply. Unfortunately, just to make things easier, product codes do not necessarily have 2 letters at the start:
are all examples of product ids...
I'm wondering if I should change the table:
CREATE TABLE `catalogue` (
`prefix` VARCHAR(3) NOT NULL,
`suffix` VARCHAR(5) NOT NULL,
PRIMARY KEY(`prefix`, `suffix`)
Then the query would something like:
SELECT `prefix`+`suffix` AS `id` FROM `catalogue` ORDER BY `prefix` AND `suffix`
What do you think?
... well more like
SELECT CONCAT(`prefix`, `suffix`) AS `id` FROM `catalogue` ORDER BY `prefix`, 0+`suffix`
which works a treat!
my job here is done
This topic is now closed. New replies are no longer allowed.