Help on order by clause
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
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
order by M.NYS_MODEL,N.MFG or (2,1)
What you want to do, i guess, is order numerically by the digits preceeding the first hyphen?
order by ... to_number(substr(NYS_MODEL,1,instr(NYS_MODEL,'-')-1))
Haven't tested it, but you get the picture.
And the need to perform such manipulations should send up a very large red flag telling you that your database is not designed correctly.
actually I didn't notice
only need to
order by M.NYS_MODEL
because AND M.MFG = 'ATCCCC'
will stay the same anyways........
Thanks for your help
Thanks for all you for your help
It worked for me
Click Here to Expand Forum to Full Width