Hi.
You cannot change the sorting behaviour of MySQL to achieve what you
want.
There are several possible work-arounds, though. One is to save the
numbers in a way (inserting zeros) that sorting will work, if that is
feasible:
5.10
5.13
5.02
5.02.08
5.03
5.27
If the depth (here: 3) of punctuation is known, you can use something like
SELECT ItemNumber
FROM Catalog
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(ItemNumber, '.', 1), '.', -1)*10000 +
SUBSTRING_INDEX(SUBSTRING_INDEX(ItemNumber, '.', 2), '.', -1)*100 +
SUBSTRING_INDEX(SUBSTRING_INDEX(ItemNumber, '.', 3), '.', -1)
(this will only work with 3.23.x)
But, if you need this kind of sorting regularly, I think the variant
to create an extra column with the values correct for sorting is the
best idea.
Bye,
Benjamin.
On Sun, Sep 30, 2001 at 02:48:40PM -0600, [EMAIL PROTECTED] wrote:
> Hi.
>
> I am trying to create a SQL statement that sorts by a column that
> contains a mix
> of numbers and periods, but it doesn't seem to work properly.
>
> The statement I use is:
>
> SELECT ItemNumber FROM Catalog ORDER BY ItemNumber
>
> For instance, here is how the list was sorted:
>
> 5.2.8
> 5.2
> 5.3
> 5.13
> 5.10
> 5.27
>
> But it should be:
>
> 5.10
> 5.13
> 5.2
> 5.2.8
> 5.3
> 5.27
>
[...]
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
