Re: Order By

On 19 Apr 2006 17:50:00 -0700, "Marshall Spight" <marshall.spight_at_gmail.com> wrote:

>Chino wrote:
>> Hi everyone,
>>
>> I have a query where i am returning a property which is a string. I am
>> trying to perform the following:
>>
>> *NB: Note the position of of the try_itemcode value 002240-10
>
>Well, this is sort of an artifact of the decision to push two integers
>together in string form as a single attribute. In light of what you
>want to do, you might want to revisit that decision.
>
>There are two widely recognized orders: numeric order for
>numeric types, and lexicographic order for strings. You're
>looking to use numeric order on two separate numeric values,
>but the values are conjoined in a string attribute. That isn't
>any kind of widely recognized order, so you DBMS won't
>know about it.
>
>I can think of a few ways to approach the problem.
>
>1) Revisit the decision to push these two separate numbers
>together (previously mentioned.)
>2) Give up on having the dbms do the sorting, and have
>the application do it. This is probably the most expedient
>but perhaps least satisfying solution.

     Agreed.

>3) If your DBMS supports it, you could try to order by the
>result of some function of the itemcode. Sorta like
>
> select ... order by first_part(item_code), second_part(item_code);

     Kludgy.

4) If youo have the choice, have the numeric string parts be of fixed size as in 002240-01, 002240-02, 002240-03, . . ., 002240-09, 002240-10, 002240-11, . . . . If you do this, make sure that you know the maximum size needed, and add leading zeroes as needed. (The above will work up to -99.)

Sincerely,

Gene Wirchenko Received on Thu Apr 20 2006 - 04:08:13 CEST