On 6/28/07, Magnus Borg <[EMAIL PROTECTED]> wrote:
Got strings in the format "integer+(char)^0..1", ex 3456, 2323, 2324A,
2324B, ...
I want to order them as they where integers, like:
order by cast(STING as unsigned) desc
But when using that query resulting in error 1292. And the result are not
correct.
How can I remove the chars before i casting the string? And if possible
take
the chars in the order.
So that i get a result like: 1000, 1000A, 1000B, 900, 800, 800A.
The queries you plan to execute influence the database design--there is no
way around that.
I would redesign the database in one of three ways:
Possibility #1: Separate the problematic columns into an integer field and
a character field, then "order by a, b".
Possibility #2: Add multiple parallel columns (used only for queries). For
example, the strings might be "800" and "1000", but the parallel integers
are 800 and 1000; ordering by the integers would get the result you want,
whereas ordering by the strings would not.
Possibility #3: Keep a single parallel column (probably an integer), mapped
such that the sort order is what you want. For example, let's assume you
have an unusual sort order such that you want numbers with no letters
displayed first, then those suffixed by "C", then all others with letters in
order. You could create a "BIGINT" assigned as follows:
a)Take the base part number, multiply by 40.
b)If there is no letter suffix, add nothing.
c)If there is a "C" suffix, add 1.
d)If there is an A suffix, add 2.
e)...
So, the part number "800C" would map to (800 * 40) + 1, the part number
"800A" would map to (800 * 40) + 2, etc.
Keep those parallel integers maintained each time you INSERT or UPDATE a
record.
Then just order by them.
#3 is by far the most powerful approach. If you know in advance what the
ordering should be, then you can design the mapping from (part number) ->
(integers) to accommodate it. (part number) -> (strings) is also possible
if you can make the strings coincide with one of the MySQL collation orders.
In general, even if you could manage to phrase the query you're describing,
it could never be efficient. The reason is that MySQL can only keep indexes
in terms of the ordinal data types and sort orders that are built-in to give
you approximately O(log N) query time. Even if you can do what you want, it
won't scale well to large tables.
You have to map the problem to the the ordinal data types that MySQL
supports (integer, string, float).
Dave.
