Re: Order By

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.
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);

HTH Marshall Received on Thu Apr 20 2006 - 02:50:00 CEST