mickyginger — 2013-02-19T06:40:36-05:00 — #1
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?
r937 — 2013-02-19T07:38:43-05:00 — #2
ORDER BY 0+SUBSTRING(productcode FROM 2)
it's a hack, but it works
mickyginger — 2013-02-19T07:55:30-05:00 — #3
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?
mickyginger — 2013-02-19T09:53:22-05:00 — #4
... well more like
SELECT CONCAT(`prefix`, `suffix`) AS `id` FROM `catalogue` ORDER BY `prefix`, 0+`suffix`
which works a treat!
r937 — 2013-02-19T14:10:36-05:00 — #5
my job here is done