Hello,
Consider, if you will, the following product codes:
BK1
BK1a
BK2
BK3
BK11
BK22
That would be that ‘natural’ order of things as far as a human is concerned.
MySQL would order them thusly:
BK1
BK11
BK1a
BK2
BK22
BK3
I can understand that logic, but how can I convince MySQL to come round to my way of thinking?
Many thanks,
M
r937
2
try
ORDER BY 0+SUBSTRING(productcode FROM 2)
it’s a hack, but it works
Hey r937,
Thanks for your reply. Unfortunately, just to make things easier, product codes do not necessarily have 2 letters at the start:
DVD1a
BK2PB
DVD1S
UMW16
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!