Re: ORDER BY

[the dragon <ceprn@HOTMAIL.COM>]

Chuck,

I have an idea, but you're not going to like it, and it will add some
overhead to the program......  You'll need to pull out your unique/distinct
values, plop them in an array, and then sort them manually with your own
rules.... and then do your selects based upon that array order.  Remember,
from an ascii viewpoint, Oracle is already sorting them correctly.  You will
end up with some horrendous code that will be difficult to code, debug, and
maintain, and I am not certain the added effort is worth the result to
basically sort the values 'out of correct order'.

Keep in mind, the customer is not always right, and usually doesn't have a
clue about what they want, let alone what they need....  Explain to them
that what they want is not 'right' and convince them (by telling them in
anhours and dollars what the development and maintainence will cost) to take
the data in the correct way.  Let us know how this turns out.

clark 'the dragon' willis


----Original Message Follows----

Hello,
   Thanks for the info.  I have tried lpad,rpad,trim plus I tried using
translate and decode through Oracle, but nothing seems to work.  I believe
it has to do with the way Oracle does the order by because I get the same
results when executing sql statements in Oracle.  The table has about 1500
items in it now and will continue to grow.  Any suggestions?

Chuck
At 03:43 PM 3/14/01 -0500, you wrote:
 >Chuck,
 >
 >Try something similar to this:
 >
 >  ORDER BY lpad(part_no,w,' ')
 >
 >where part_no is the name of the column, and w is the maximum
 >width of the part_no field.
 >
 >Ray
 >
 >> Hello!
 >>   I am trying to use an order by clause on a char field, but the order
 >> doesn't come out quite like the customer wanted.  For example, I have
the
 >> values: 1,1SL,1SM,1UP,100,197,198,199 and 1000.  The order by clause in
SQR
 >> produces the following order:
 >>
 >> 1
 >> 100
 >> 197
 >> 198
 >> 199
 >> 1SL
 >> 1SM
 >> 1UP
 >> 1000
 >>
 >> I would like the order to be 1,1SL,1UP,100,197,198,199,100.  Does anyone
 >> have any suggestions?
 >>
 >> Thank you,
 >>   Chuck
 >>
 >

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com