Help on order by clause

[orakle]

Hi All,

I have a situation like the i want to display nys_model in order by
But my query is not giving order by
if you see the nys_model 020,021,1000,300,989
but i need to get values like 020,021,300,989,1000

any help is appreciated


1 SELECT N.MFG, M.NYS_MODEL, M.BC, M.MFG_MODEL, M.DRAW_NO, M.TRACK_SEAT, M.SEAT_CAP, M.WC
2 FROM BUS.MANUFACTURER N, BUS.MODEL M
3 WHERE N.MFG = M.MFG
4 AND M.MFG = 'ATCCCC'
5* ORDER BY 1,2
SQL> /

MFG NYS_MODEL B MFG_MODEL DRAW_NO T SE WC
------ ------------------------- - ---------- --------------- - -- --
ATCCCC 020-NY-30-00WC-ATCCCC TEST1 TEST1 Y 30 0
ATCCCC 021-NY-30-00WC-ATCCCC TEST2 TEST2 Y 30 0
ATCCCC 1000-NY-30-00WC-ATCCCC TEST6 TEST6 Y 30 0
ATCCCC 300-NY-30-00WC-ATCCCC TEST4 TEST4 Y 30 0
ATCCCC 989-NY-30-00WC-ATCCCC C TEST5 Y 30 0

[eugene]

try

order by M.NYS_MODEL,N.MFG or (2,1)

eugene
OCP

[orakle]

Still gives same result

[slimdave]

What you want to do, i guess, is order numerically by the digits preceeding the first hyphen?

like ...

order by ... to_number(substr(NYS_MODEL,1,instr(NYS_MODEL,'-')-1))

Haven't tested it, but you get the picture.

[chrisrlong]

And the need to perform such manipulations should send up a very large red flag telling you that your database is not designed correctly.

- Chris

[eugene]

actually I didn't notice
only need to

order by M.NYS_MODEL

because AND M.MFG = 'ATCCCC'
will stay the same anyways........

Eugene
OCP

[orakle]

Thanks for all you for your help

It worked for me