Help on order by clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Help on order by clause

  1. #1
    Join Date
    May 2003
    Location
    NY
    Posts
    24

    Help on order by clause

    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

  2. #2
    Join Date
    Jan 2001
    Location
    San Diego, CA
    Posts
    81
    try

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

    eugene
    OCP

  3. #3
    Join Date
    May 2003
    Location
    NY
    Posts
    24

    help again

    Still gives same result

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Jan 2001
    Location
    San Diego, CA
    Posts
    81
    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

  7. #7
    Join Date
    May 2003
    Location
    NY
    Posts
    24

    Thanks for your help

    Thanks for all you for your help

    It worked for me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width