Results 1 to 9 of 9

Thread: odd sort order

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: odd sort order

    We have a file that we currently receive in a strange sort order. On an alphanumeric column, characters are higher in the sort than numbers (did I say that correctly?)

    So an example of the data would look like
    Code:
    ABC123
    CDF123
    123ABC
    123CDF
    Is there an ALTER SESSION setting that I can use so that any ORDER BY expressions that I issue would follow this sort order?

    Right now I get
    Code:
    SQL> ed
    Wrote file afiedt.buf
    
      1  select 'ABC123' from dual
      2  union all
      3  select '123ABC' from dual
      4  union all
      5  select 'CDF123' from dual
      6  union all
      7  select '123CDF' from dual
      8* order by 1
    SQL> /
    
    'ABC12
    ------
    123ABC
    123CDF
    ABC123
    CDF123

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Alphanumeric column" - is it a VARCHAR2 datatype column? If so, I created a table, inserted your sample data and ORDER BY sorted it correctly.

    If it is not a VARCHAR2, what is it then? Is it a LONG datatype, perhaps? It can not be ORDERED BY, so data can be selected only in the default order (rownum? which would represent order in which data was stored into the table).
    CLOB can not be selected (in SQL*Plus) at all (in a way SELECT clob_column FROM some_table).

    I really wouldn't know is there an ALTER SESSION possibility that will reverse sort order ...

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    It's a VARCHAR2.

    Wait, when you sorted the data, it appeared like the following, with letters sorting before numbers?

    ABC123
    CDF123
    123ABC
    123CDF

    -Chuck

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try:
    Code:
     ALTER SESSION SET NLS_SORT=GERMAN;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, NLS_SORT=GERMAN won't change much, I'm afraid. But NLS_SORT=BINARY might

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Littlefoot
    Hm, NLS_SORT=GERMAN won't change much, I'm afraid. But NLS_SORT=BINARY might
    Check it out:
    Code:
    SQL> ALTER SESSSION SET NLS_SORT=GERMAN;
    
    Session altered.
    
    SQL> select 'ABC123' a from dual
      2  union all
      3  select '123ABC' from dual
      4  union all
      5  select 'CDF123' from dual
      6  union all
      7  select '123CDF' from dual
      8* order by 1
    SQL>/
    
    A
    ------
    ABC123
    CDF123
    123ABC
    123CDF
    
    SQL>ALTER SESSION SET NLS_SORT=BINARY;
    
    Session altered.
    
    SQL>select 'ABC123' a from dual
      2  union all
      3  select '123ABC' from dual
      4  union all
      5  select 'CDF123' from dual
      6  union all
      7  select '123CDF' from dual
      8  order by 1
      9  /
    
    A
    ------
    123ABC
    123CDF
    ABC123
    CDF123

    PS: The question was: "Is there an ALTER SESSION setting that I can use so that any ORDER BY expressions that I issue would follow this sort order?"

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    LOL, more I read this thread, less I understand what Chuck wants. I thought that he, by issuing "SELECT col FROM table ORDER BY col" currently gets

    ABC123
    123ABC

    but he wanted

    123ABC
    ABC123.

    I guess we misunderstood, LKBrwn_DBA ... do we agree that NLS_SORT=GERMAN will give ABC, 123 sort order, and NLS_SORT=BINARY will give 123, ABC as the result? I hope that's correct ...

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Sorry..sorry..sorry...
    I thought my careful use of examples made my question clear, but it obviously did not.

    We currently receive the sort order:

    123ABC
    123CDF
    ABC123
    CDF123

    We want to get

    ABC123
    CDF123
    123ABC
    123CDF

    We are currenlty in the process of sorting the data using NLS_SORT=German against the entire table to see if it works.

    Thanks for the help,
    Chuck

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, I'm sorry - I completely misunderstood you, Chuck. Sorry for wasting your time too, LKBrwn_DBA.

Posting Permissions

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