0

I've searched for this for a long time, but the solutions I've found aren't working as I need.

Let me explain: I have a table containing a couple of thousands of products, each one with an alphanumeric SKU, used also for sorting. This SKU consists of:

Category Code (variable number of alphabetic characters),

Product Number (integer),

Product Model Variation (optional, variable number of alphabetic characters)

For example: MANT 12 CL (without spaces)

Now, I need to get them ordered like this (and if these were filenames, OSX Finder would order them perfectly):

MANT1
MANT2
MANT2C
MANT2D
MANT2W
MANT3
MANT4C
MANT9
MANT12
MANT12C
MANT12CL
MANT12P
MANT13
MANT21
MANT24
MANT24D
MANT29

Of course ORDER BY sku is plainly wrong:

MANT1
MANT12
MANT12C
MANT12CL
MANT12P
MANT13
MANT2
MANT21
MANT24
MANT24D
MANT29
MANT2C
MANT2D
MANT2W
MANT3
MANT4C
MANT9

And ORDER BY LENGTH(sku), sku has problems sorting the model variations:

MANT1
MANT2
MANT3
MANT9
MANT12
MANT13
MANT21
MANT24
MANT29
MANT2C
MANT2D
MANT2W
MANT4C
MANT12C
MANT12P
MANT24D
MANT12CL

So, is there a way to sort this stuff like Finder would?

(Also, once sorted, is there a way to get the next and previous product? I don't mind using several queries: at this point elegance is the last of my problems...)

Thanks everybody in advance.

One last thing: during my searches I've found this answer to a similar question but I have no idea how to use it in PHP, so I don't know if it works and is actually an answer to my question.

Community
  • 1
  • 1
Copons
  • 3
  • 1

2 Answers2

1

Are you using PHP when fetching data?

If so, try using natural sort function for in memory sort after data is already loaded?

Pavle Gartner
  • 659
  • 1
  • 7
  • 21
  • Oh man, it was so simple yet I spent dozens of days and `natsort()` never occurred to me. Thank you very much! – Copons Mar 21 '13 at 13:32
0

The order is not 'plain wrong', it simply depends what collation you use. In your case, you might try the binary collation, for example, 'latin1_bin'.

Following example the ORDER BY using COLLATE for UTF8 data:

mysql> SELECT c1 FROM t1 ORDER BY c1;
+------+
| c1   |
+------+
| a1   |
| a12  |
| a13c |
| a2   |
| a21  |
+------+

mysql> SELECT c1 FROM t1 ORDER BY c1 COLLATE 'utf8_bin';
+------+
| c1   |
+------+
| a1   |
| a12  |
| a2   |
| a21  |
| a13c |
+------+
geertjanvdk
  • 3,440
  • 24
  • 26
  • This actually doesn't work as I need: `a2` should come after `a1` and `a13c` should come after `a12`, but thanks anyway! – Copons Mar 21 '13 at 13:34